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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.