Click here to Skip to main content
15,671,597 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
    Application.WindowState = xlMinimized
    Application.WindowState = xlMinimized
    Application.WindowState = xlMinimized
    Application.WindowState = xlMinimized

End Sub
Updated 12-Aug-11 1:16am
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:
ActiveWorkbook.SaveAs Filename:="yourpath\Book2.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
Share this answer
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:
It means:
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