Solved Copy sheets to new workbook

February 17, 2012 at 09:12:34
Specs: Windows 7
Need a macro to copy sheet 1 an all of the content, to an another existing workbook,
and to the first free sheets in the another workbook.

For example:copy from book 1 sheet 1, to the first available sheet in Book 2
if there is data in sheet 1, paste in sheet 2, and further.

Does anyone have an idea?


See More: Copy sheets to new workbook

Report •


✔ Best Answer
February 20, 2012 at 17:38:21
Since you can't write to a closed workbook, this code will open "The Other Book", copy Sheet1 of "The First Book" to "The Other Book" as the last sheet, save and close "The Other Book", then clear Sheet1 of "The First Book".

You'll need to edit the workbook names and path to make it work for you.

Sub CopySheet1()
'Open the other book
    Workbooks.Open Filename:="C:\Users\user\Documents\The Other Book.xlsm"
'Copy Sheet 1 of the first book to the end of the other book
    Workbooks("The First Book.xlsm").Sheets(1).Copy _
     After:=Workbooks("The Other Book.xlsm").Sheets(Sheets.Count)
'Save and close the other book
    ActiveWorkbook.Close SaveChanges:=True
'Clear Sheet1 of the first book
    Workbooks("The First Book.xlsm").Sheets(1).Cells.ClearContents
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 17, 2012 at 15:55:37
What happens if all of the sheets in Book 2 already contain data?

As more and more sheets are copied into it, I would imagine that eventually all of the sheets will be filled.

What would be your plan once that happens?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 19, 2012 at 03:06:05
Hey, thanks for your question

The plan is that the sheet 1 in book 1 is used as a template, while Book 2 will be used for a year at a time, I will for example have a "bok2" for 2012 a "Book 2" for 2013 and forward. "Book 2 will contain approximately 500 sheets.
Want it to work something like this:
When you press "save", copy sheet 1 to the first available sheets in book 2, and data in book 1 is cleared.
If Book 2 is full, a message box "Book 2 is full"

Velldig grateful if someone can post code examples


Report •

#3
February 19, 2012 at 06:02:20
If the sheets in Book 2 are just copies of the sheets in Book1, why not just add them as new sheets after the last sheet in Book 2?

Why start with 500 empty sheets? What if you created Book2, deleted all sheets except for Sheet 1, manually copied Book1!Sheet1 as a start (or used Book2!Sheet1 as a title page or something) and then let the macro keep adding new a new sheet each time a sheet from Book1 was copied?

My other question relates to using Save to initiate the copy. In many cases I save my workbooks more than one time during a session so that I don't lose incremental changes as I'm working. If the sheet was copied with each save, there would be "incomplete" copies in Book2. Is that going to be an issue in your situation?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 20, 2012 at 01:02:19
Very good
Of course a lot better to let macro keep adding new and new sheets .

Using” save” to initiate the Copy: youre absolutely right, that going to be a issue.
I think it`s better to assign a button in book 1 sheet 1 called “transfer to book 2” whith a msg. books that ask “are you sure” yes/no
If yes:
new sheet is added in book 2
sheet 1 Book 1 copied and pasted into free sheet Book 2
sheet 1, Book 1 is cleared for data
If no:
The procedure ending, and you can continue to work on in book 1

Its also possible that Book 2 is closed, so the macro must also handle that.


Report •

#5
February 20, 2012 at 17:38:21
✔ Best Answer
Since you can't write to a closed workbook, this code will open "The Other Book", copy Sheet1 of "The First Book" to "The Other Book" as the last sheet, save and close "The Other Book", then clear Sheet1 of "The First Book".

You'll need to edit the workbook names and path to make it work for you.

Sub CopySheet1()
'Open the other book
    Workbooks.Open Filename:="C:\Users\user\Documents\The Other Book.xlsm"
'Copy Sheet 1 of the first book to the end of the other book
    Workbooks("The First Book.xlsm").Sheets(1).Copy _
     After:=Workbooks("The Other Book.xlsm").Sheets(Sheets.Count)
'Save and close the other book
    ActiveWorkbook.Close SaveChanges:=True
'Clear Sheet1 of the first book
    Workbooks("The First Book.xlsm").Sheets(1).Cells.ClearContents
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 20, 2012 at 23:36:05
Works perfectly
Thanks a lot

Report •

Ask Question