Excel help

raptormonkey

raptormonkey

raptormonkey

Permabanned
Joined
14 Jun 2009
Posts
1,110
I have produced one 'sheet' with weekly data on it in Exel. Now, i want to make 52 'Sheets' available. Is there an easy way to do this rather than selecting sheet 2 and then pasting all the contents of sheet one on it and doing every 52 sheets the same!! Thanks
 
ok, will try and come up with something to do exactly what you want. Press alt+F11 to enter the VB editor, then select 'thisworkbook' from the left hand side, and copy and paste the below, then save.

Code: 
Sub copySheet2()
   
   Dim i As Integer
   Dim counter As Integer
   counter = 1
   Do Until counter = 53
       i = ThisWorkbook.Sheets.Count
       Sheets("Sheet1").Copy After:=Sheets(i)
       ThisWorkbook.Sheets(i + 1).Name = "Week " & counter
       counter = counter + 1
   Loop
End Sub

Then go to macros, and select 'thisworkbook.copysheet2' or similar and choose run.

Should populate the additional sheets. (I've tested the above in excel 2007 and it works, let me know if it doesn't work in yours)
 
Am i allowed to kiss you online?

Now, each sheet has been saved i will be adding different data to each page so i don't want the 'data' to be saved the same on each 'Sheet' as each week will produce different data. If i save one sheet, will the data be saved on all the others? I don't want this to happen as the data for each week will vary.
 
Last edited:
raptormonkey said:
Am i allowed to kiss you online?

Now, each sheet has been saved i will be adding different data to each page so i don't want the 'dtat' to be saved the same on each 'Sheet' as each week will produce different data.
Click to expand...

if the sheet you copied already had data in, you're going to need to delete it out manually, I'm afraid. Trying to strip out the data automatically is a bit of a nightmare.

You could copy the first sheet to a new workbook, delete the data, then run the macro to just copy the template into each sheet, then populate the first week again...
 
That is what i have done. Made a template, run the Macro as you instructed. This has produced 52 sheets for me.

Now, as i work on one sheet at a time (per week) and then save it, it won't put that data onto the other 51 sheets will it? I don't want that as each week will be different. Thanks mate
 
No, the macro won't do anything further unless you run it again, and then it would probably error because the named sheets already exist.
 
You must log in or register to reply here.
Back
Top Bottom