Macro to copy sheets

  • Thread starter Thread starter FGOMEZ
  • Start date Start date
F

FGOMEZ

Every month I have to copy about 35 sheets for 17 different people,
sometimes is only 1 sheet but the maximun could be 15 sheets.
Could I have a macro to select the sheets (any number of sheets) and do a
copy to a new sheet(s).
I tried to do with the Macro record, but unfortunately comes with the sheet
name.
Any help would be appreciated.

Fernando
 
I'd definately use a macro, esp if the source/target sheets dont change.
Need more details to code it, but here's a start. Experiment with a copy of
your file because macros are not subject to the Undo command.

sub copysheets()
'Copy Source1 after the last sheet then renames the tab to Person1 for
easier reference later.
'Consider stacking the lines together using a colon (:) between for easier
reading as shown for Person2
' ie: individual getting 7 sheets copied would thus have 7 lines instead of
14. No speed difference.
'------------------ Person #1 gets one sheet ------------------
Sheets("Source1"").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Person1"
'------------------ Person #2 gets two sheets ------------------
Sheets("Source1"").Copy after:=Sheets(Sheets.Count):
Sheets(Sheets.Count).Name = "Person2"
Sheets("Source2"").Copy after:=Sheets(Sheets.Count):
Sheets(Sheets.Count).Name = "Person2b"
'------------------ etc ------------------
end sub

BTW, presume at some point you'll need to change which sheet is being
formatted/etc by your code, in which case the following line makes Person1
active for any lines following it until changed to another sheet.
Sheets("Person1").Activate
 
Back
Top