Click here to Skip to main content
14,735,271 members
Please Sign up or sign in to vote.
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 :

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
Updated 12-Aug-11 2:16am
v3
Comments
Slacker007 12-Aug-11 7:17am
   
Edited for readability and tags. Did not edit grammar or spelling.

1 solution

I just tried a macro recording session and it worked fine giving me:
Sheets("Location").Select
Sheets("Location").Copy
ActiveWorkbook.SaveAs Filename:="yourpath\Book2.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
   
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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900