How to generate a new excel WorkBook using macro

Question

0.00/5 (No votes)

See more:

I want to copy data in one sheet of workbook to another

I want to generate a new workbook based on sheet of data in another workbook. How record or use I use macro to do this task, for reference I am attaching the excel sheet please have a look. The excel sheet is site.xlsx, this work book contains two sheets 1. Location 2.Name

I want to create a macro which should generate new workbooks which should contain only location details copied from site.xlsx is it possible? I tried record macro following error(out of range) is coming please help me, I have noted the macro code below.

please note :

VB

Sub MacroLoc()
'
' MacroLoc Macro
' 3.51 pm
'
' Keyboard Shortcut: Ctrl+k
'
    Sheets("Location").Select
    Sheets("Location").Copy
    Application.WindowState = xlMinimized
    Application.WindowState = xlMinimized
    Windows("Site.xlsx").Activate
    Application.WindowState = xlMinimized
    Windows("Book1").Activate
    Application.WindowState = xlMinimized
    Windows("Site.xlsx").Activate

End Sub

Posted 12-Aug-11 0:38am

Ajain A K

Updated 12-Aug-11 1:16am

Slacker007

v3

Add a Solution

Comments

Slacker007 12-Aug-11 7:17am

Edited for readability and tags. Did not edit grammar or spelling.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2011-08-12T02:34:00

Solution 1

I just tried a macro recording session and it worked fine giving me:

VB

Sheets("Location").Select
Sheets("Location").Copy
ActiveWorkbook.SaveAs Filename:="yourpath\Book2.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

Posted 12-Aug-11 2:34am

Richard MacCutchan

Comments

Ajain A K 12-Aug-11 11:45am

Hai Richard,

Thanks a lot it is good and working fine.

Richard MacCutchan 13-Aug-11 7:18am

Don't forget to "Accept Answer" to show your problem is solved.

Maciej Los 14-Aug-11 15:30pm

This code is incorrect, because has no context. I would not recommend to use it when "Site.xlsx" is not active workbook. Much better way is to use it in this way:
ThisWorkbook.Worksheets("Location").Copy
It means:
Workbooks("Site.xlsx").Worksheets("Location").Copy.
Even if workbook "Site.xlsx" will not be active, code will be executed correctly.

Richard MacCutchan 15-Aug-11 3:28am

I guess you did not read the original question and so missed the context.

How to generate a new excel WorkBook using macro

1 solution

Solution 1

Add your solution here

Preview 0

How to generate a new excel WorkBook using macro

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us