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

dearnemo
dearnemo used Ask the Experts™
on
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"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
try macro below:
Sub CopySheet()
    Sheet1.Copy
    ActiveWorkbook.SaveAs Filename:="C:\desktop\abc.xlsx", FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
End Sub

Open in new window

Author

Commented:
Thanks wchh. It worked. :)

I have a question which might have a simple answer that I m not aware of right now.
I have a vba macro, which on running each time, adds a new worksheet to the active workbook. In that new worksheet it fills in data as a result of my vba code. So right now, each time a macro runs, its creating new worksheet starting with sheet 4, sheet5, and so on...
Is there a way to force a macro to not add a new worksheet but write/fill in data to existing sheet1? So new scenario will be that each time a macro runs, it overwrites the sheet1. How can I do that?

Commented:
Please post this as new question. thanks.

Author

Commented:
Thanks for your suggestion. I will create a new post for this question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial