Copying worksheets to a different workbook

[Chr1sUK]

I need to add worksheets to 14 different workbooks for weekly tabs from the start of July - End of August.

Naturally, I would rather not have to do this all manually, 14 times, as this would take ages.

There only seems to be a move or copy option, but that doesn't seem to do the trick.

Surely Bill wouldn't have missed out such a useful feature??

TIA.

 

[Molby]

Select all 14 sheets and right click on one of them, select copy, and then select the destination workbook under the option 'To book'. Does this not work?

 

[Chr1sUK]

actually, it does work lol.

I just did it wrong the first time!

Thanks

 

[Chr1sUK]

OK then, how about this....

Would there be a way to automatically add sheets for the end of each working week until the end of 2004?

Or, if not, I could do this once manually, would I then be able to create a macro to add all the sheets to the end of each workbook that I want them to go in?

That would certainly save loads of time.

 

[Molby]

Both of your suggestions should be possible via VBA, but can I ask why you are splitting the data out onto separate worksheets? It is much better to have it all on one sheet, and then query from that.

 

[Chr1sUK]

Its a business spreadsheet, each worksheet reflects the end of the working week,and each workbook is for a different region, and certain changes etc. that have occurred in that week.

It would be very unclear and confusing to have so much data on one worksheet, there is around 2 years worth already.

So in this case it is definately not better to have it all on one sheet

 

[KenWright]

The downside is that you can't really use Pivot tables with your data in that format. If it were all on a single sheet initially you would be able to use Pivot tables to create pretty much any report you like, including separate sheets if necessary for reporting purposes for each week in seconds. You would also have an enormous amount of flexibility that you don't currently have, wrt creating other reports and doing various data analysis.

That having been said, if your data is all in the same format on each sheet, we can give you a routine that will instantly combine all sheets into a master sheet at any time and then allow you to use a Pivot table against the data effectively, eg:-

Sub SummaryCombineMultipleSheets()

Dim SumWks As Worksheet
Dim sd As Worksheet
Dim sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long

HeadRow = InputBox("What row are the Sheet's data headers in?")
DataRow = HeadRow + 1

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set SumWks = Worksheets.Add

With SumWks
   .Move Before:=Sheets(1)
   .Name = "Summary Sheet"
    Sheets(2).Rows(HeadRow).Copy .Range("1:1")
    Columns("A:A").Insert Shift:=xlToRight
    Range("A1").Value = "INDEX"
End With

With Sheets(2)
    ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
End With

For sht = 2 To ActiveWorkbook.Sheets.Count
   Set sd = Sheets(sht)
   lrow1 = SumWks.Cells(Rows.Count, "B").End(xlUp).Row
   lrow2 = sd.Cells(Rows.Count, "B").End(xlUp).Row
   sd.Activate
   sd.Range(Cells(DataRow, 1), Cells(lrow2, ColW)).Copy SumWks.Cells(lrow1 + 1, 2)
   SumWks.Cells(lrow1 + 1, 1).Resize(lrow2 - (DataRow - 1), 1).Value = sd.Name
Next sht

SumWks.Activate

End Sub

This doesn't affect your existing data in anyway, and simply gives you more options.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------