VBA - Loop through worksheets and paste to a new workbook

  • Hello, I need help with looping macro. I created below macro by recording it and works just fine. It is not as dynamic as I want it to be. If I need to add/delete worksheet, I will have to edit the macro which I don't want to do. What I need is to modify the following macro using a looping macro. Instead of using sheet(arrays) to list all the sheets in the macro, a name range, such as "reportsheets", would be more dynamic, more easy to update any changes to reporting sheet names. The list of "reportsheets" would be in the workbook in sheet name "MacroSheet". The workbook has about 95 sheets that includes all the data and reporting sheets.

    The macro does the following:
    - Make copy of all the "reportsheets" and paste special value to a new workbook
    - Deletes name ranges in the new workbook except for "Print_Title" & "Print_Area" which I need
    - Saves the file referencing "PathSave & FilenameSave" for file name and close
    - Keeps the original workbook open

    I would like a loop macro to do the above function. Hope I was clear enough.
    Let me know if any further questions for clarification. This is my first time posting on the forum.

  • I can't attach a sample copy of the workbook. Confidential data in it and all the headers and titles reference the company.

    I don't know much about writing vba codes. The one I did was a recorded macro. So modifying it to make it more efficient is where I fall short.

    The file name and path is in sheet name: "MacroSheet". It is also where I added the macro button. Can you still help without the sample workbook?

    Thanks royUK.

  • Try this

  • Thanks... royUK. This works but it deletes all the name ranges. I would like to keep "Print_Area" & Print_Titles" name ranges only. Also is it possible to use a loop to cycle through a list of the report sheets names instead of having it in an array? The list would be in the "MacroSheet" and named "ReportSheets". That is what I am look for is to make it more dynamic, where I can add/subtract report sheets and not have to go into the macro to make any changes.

  • updating my prior reply royUK. I saw where you want to keep "Print_Titles" & "Print_Area". It just didn't work. When I ran the macro, it deleted all the name ranges.....sorry for not realizing it earlier.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!