how to copy excel sheet from one workbook and paste into another workbook, save it ?

[dearnemo385]

I have an excel file which has some data in sheet1. I want to write a macro to perform certain activities:
1. Copy all the data in sheet1.
2. Invoke another instance of Excel application
3. paste all the data including headers from 1.) into that new workbook's sheet1.
4. Save the new workbook under specified drive "c:\desktop\abc.xlxs"

[galvinpaddy]

Hi,

If i read correctly, you will need to paste the following into a new module.

Please Login or Register  to view this content.

The part that reads XXXXXX is where you need to type your username (windows login name if business, or machine name if personal)
and the part that reads abc.xls - all you need to change here is the ABC part, to the required file name you want it to save as.

My macro works on copying the entire sheet1 data and pasting the values only (not formulas incase you have any)

galvinpaddy

[dearnemo385]

Thanks a lot Galvin.

It worked. Now I need to add some dynamicity (right word?) here in my code.
Actually I have another module let's say module1. Each time I run that module1, it adds data from SharePoint to new worksheet. Since Excel has three sheets worksheet1, worksheet2 and worksheet3 by default, my macro vba adds data(which it basically imports from SharePoint) to worksheet starting at worksheet4. Each time I run this macro, it adds the data to worksheet5, 6 and so on.
I will not have any data in the rest of the sheets and wouldn't need any sheets except the most recent one produced by my vba macro. And I would want to refer that sheet dynamically to the code you listed. So Sheet1 below should be dynamic and would be the most recent one produced by my macro. How do I do it?
Sheets("Sheet1").Select

Also each time I run your macro which is in module2, it asks user 'do you want to replace abc.xls?' because its obvious that the file is already created when you first run module2. How can we make sure that once abc.xls is created, it will overwrite the existing one w/o asking a user 'do you want to replace abc.xls?'

I would have to automate to run these two macros one after the other everyday w/o opening Excel file in the future. Any help/guidance is much appreciated. Thanks again.

[galvinpaddy]

Unfortunately, my experience on Excel & VBA does not go far enough to answer your questions this time round

I think you may be better off with a guru to support.
Sorry fella.

galvinpaddy

[dearnemo385]

Thanks Gavin. Your code gave me some insights to get my questions.
I could write a script to solve my second question
"Also each time I run your macro which is in module2, it asks user 'do you want to replace abc.xls?' because its obvious that the file is already created when you first run module2. How can we make sure that once abc.xls is created, it will overwrite the existing one w/o asking a user 'do you want to replace abc.xls?' "

by adding Application.DisplayAlerts = False
before saving the active workbook and

Application.DisplayAlerts = True after saving the active workbook.

My another question might have a simple solution which I am not aware of right now.
Each time I run my macro, it adds a new worksheet to my active workbook. Let's say sheet4. Is there a way to force a vba macro to write data to only sheet1. Doesn't matter if it overwrites the sheet1 each time a macro runs. Any thoughts?
Thanks.