Rename Worksheets and Copy Data

[SAMUSER]

Hi everyone,

I need to copy data from one workbook- all of it, containing multiple worksheets. This needs to be pasted onto a new workbook with the same worksheet names as the previous workbook.

I need help with copying the names of the worksheets and pasting them onto the new workbook. And frankly, I am hitting a dead end. Any help would be appreciated.

Thanks,
Saim

 

[AlphaFrog]

• Select the first sheet
• Shift+Click on the last sheet's tab to select all the sheets
• Right-click on one of the selected sheet tabs
• Select Move or Copy from the pop-up context menu
  • To book: (new book)
  • Check the Create a copy checkbox
  • OK

 

[SAMUSER]

Hi Alpha Frog,

Thanks for the help. I am looking to do this multiple times. Therefore, I need to use a macro for this process. I have gotten some code fromt he recorder, but I need to generalize it so that it asks the user the location of the file (from which data is being copied), activates the file, copies everything, and then pastes in the destination folder.

This is what I have to copy the sheets and paste them so far:

' Macro4 Macro
    Sheets("Income Brackets").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=20
    Sheets(Array("Income Brackets", "Program Data", "Impact Map", "Participant Names", _
        "Parameters", "Stakeholders", "Subjects", "Employees", "Year Data", "Participants", _
        "Subjects - Program Data", "MoH - Program Data", "Federal Govt - Program Data", _
        "Employees - Program Data")).Select
    Sheets("Employees - Program Data").Activate
    Sheets(Array("Income Brackets", "Program Data", "Impact Map", "Participant Names", _
        "Parameters", "Stakeholders", "Subjects", "Employees", "Year Data", "Participants", _
        "Subjects - Program Data", "MoH - Program Data", "Federal Govt - Program Data", _
        "Employees - Program Data")).Copy Before:=Workbooks("Book2").Sheets(18)
End Sub