Help with loop through copy multiple sheets and paste values to a new workbook and save

[sam20]

Hello, I need help with the macro. I recorded the macro and it works 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 loop and using a name range, such as "reportsheets", for those sheets listed within the workbook. 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.

>>> use code - tags <<<

Dim PathSave As String
Dim FilenameSave As String

    PathSave = Range("B3").Value
    FilenameSave = Range("B4").Value

Application.ScreenUpdating = False

    Sheets(Array("Cover", "Sum1", "Sum2", "Comp1", "Mix1", "StrWk1", "Comp2", "Mix2", _
        "StrWk2", "Comp3", "Mix3", "StrWk3", "Comp4", "Mix4", "StrWk4", "Comp5", "Mix5", _
        "StrWk5", "Comp6", "Mix6", "StrWk6", "Comp7", "Mix7", "StrWk7", "Comp8", "Mix8", _
        "StrWk8", "Comp9", "Mix9", "StrWk9", "Comp10", "Mix10", "StrWk10", "Comp11", "Mix11", _
        "StrWk11", "Comp13", "Mix13", "StrWk13", "Comp14", "Mix14", "StrWk14", "Comp15", _
        "Mix15", "StrWk15", "Comp17", "Mix17", "StrWk17", "Comp18", "Mix18", "StrWk18", _
        "Comp19", "Mix19", "StrWk19", "Comp20", "Mix20", "StrWk20", "Comp21", "Mix21", _
        "StrWk21")).Select
    Sheets("Cover").Activate
    Sheets(Array("Cover", "Sum1", "Sum2", "Comp1", "Mix1", "StrWk1", "Comp2", "Mix2", _
        "StrWk2", "Comp3", "Mix3", "StrWk3", "Comp4", "Mix4", "StrWk4", "Comp5", "Mix5", _
        "StrWk5", "Comp6", "Mix6", "StrWk6", "Comp7", "Mix7", "StrWk7", "Comp8", "Mix8", _
        "StrWk8", "Comp9", "Mix9", "StrWk9", "Comp10", "Mix10", "StrWk10", "Comp11", "Mix11", _
        "StrWk11", "Comp13", "Mix13", "StrWk13", "Comp14", "Mix14", "StrWk14", "Comp15", _
        "Mix15", "StrWk15", "Comp17", "Mix17", "StrWk17", "Comp18", "Mix18", "StrWk18", _
        "Comp19", "Mix19", "StrWk19", "Comp20", "Mix20", "StrWk20", "Comp21", "Mix21", _
        "StrWk21")).Copy
    Sheets(Array("Cover", "Sum1", "Sum2", "Comp1", "Mix1", "StrWk1", "Comp2", "Mix2", _
        "StrWk2", "Comp3", "Mix3", "StrWk3", "Comp4", "Mix4", "StrWk4", "Comp5", "Mix5", _
        "StrWk5", "Comp6", "Mix6", "StrWk6", "Comp7", "Mix7", "StrWk7", "Comp8", "Mix8", _
        "StrWk8", "Comp9", "Mix9", "StrWk9", "Comp10", "Mix10", "StrWk10", "Comp11", "Mix11", _
        "StrWk11", "Comp13", "Mix13", "StrWk13", "Comp14", "Mix14", "StrWk14", "Comp15", _
        "Mix15", "StrWk15", "Comp17", "Mix17", "StrWk17", "Comp18", "Mix18", "StrWk18", _
        "Comp19", "Mix19", "StrWk19", "Comp20", "Mix20", "StrWk20", "Comp21", "Mix21", _
        "StrWk21")).Select
    Sheets("Cover").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("Cover").Select
    ActiveWorkbook.Names("District").Delete
    ActiveWorkbook.Names("DistrictName").Delete
    ActiveWorkbook.Names("FiscalWks").Delete
    ActiveWorkbook.Names("FYLY").Delete
    ActiveWorkbook.Names("FYTY").Delete
    ActiveWorkbook.Names("LOCATN").Delete
    ActiveWorkbook.Names("NonComp").Delete
    ActiveWorkbook.Names("PdWk").Delete
    ActiveWorkbook.Names("Period").Delete
    ActiveWorkbook.Names("StoreList").Delete
    ActiveWorkbook.Names("week").Delete
    ActiveWorkbook.Names("WkEndDate").Delete
    ActiveWorkbook.Names("WkNum").Delete
    ActiveWorkbook.SaveAs PathSave & FilenameSave, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    Sheets("MacroSheet").Select
    Range("A15").Select

Application.ScreenUpdating = True

End Sub

 

[vletm]

sam20
Could You send a sample file which has Your ... sheets ... without data?
... why do You select > copy > select again ... same sheets (three times)?
... as well as those deleting ... could do different way?

 

[sam20]

The macro was recorded. I did not write it. So it may seem to look busy.....not clean. I had to select the name ranges that was not "Print_Title" & Print_Area" and delete. This is way it shows up in the macro when I recorded it. That is why I need help to make this macro more efficient. Need a loop macro to got through all report sheets and copy paste special value to a new workbook and delete name ranges excluding the "Print" ones as mentioned before. I can't send a sample. Confidential data in it and all the headers and titles reference the company. Can you still help without a sample file?
Thanks...@vletm

 

[vletm]

sam20
A sample file ...hmm? ... is a sample file?
Nobody here isn't interesting about normal data or so!
Create a new Excel-file which has needed named worksheets .. which has needed named ranges (and few more).
Without a sample file, it is a challenge to test any modified code.

 

[sam20]

I am attaching a sample workbook that has all the report sheets and the "MacroSheet" where it contains the macro button. It also includes list of all the report sheets and the recorded macro. Name ranges are included but now it has #Ref because I have deleted all other data/input sheets.
Hope this helps with your testing. Thank you so much.....@vletm.

 

[vletm]

sam20
I did quick check ...
As You wrote,
a) ranges are missing - then I cannot even test how do You macro would work.
b) deleted other sheets - if those 'no need' for that macro then okay, but then Your sample isn't as Your normal file.
I tested ...
c) that copy and paste -part - nothing copied! If nothing to copy, then it's a challenge to verify, how do this works.
d) You have used ActiveX-component ... is it that button? I do not use those! ( = file will be read-only)

You seems to try to copy ... a lot ... and after that You delete a lot.
> Question: Have You tried to 'copy and paste' only needed ranges?

 

[sam20]

The macro works in the sample workbook that I attached. I tested it before attaching the sample file. I had to delete all the other worksheets that contained data and other sheets that the report would be picking it up from. The button is ActiveX control. Yes, my sample is not my normal working file. I had to do this way due to confidential info in my working file. Did you just try the macro first and does it work as is? The result is what I am looking for, but instead of having those report sheets in an array, I would like to be in a list on the "MacroSheet" where I can make changes if necessary. I don't want to go in the macro to make any changes. I would like the macro to be modified using loop, which then it would cycle through the list and do what I mention before.

 

[vletm]

sam20
Based my previous:
a) ... missing ranges means cannot test
c) ... there should be some sample data that could see that it works
d) ... read-only; I can do other file - okay

Your looking for:
the 'Macrosheet' has needed sheet names - it should be possible, I could try to check that, but as above - I cannot test!
... here my sample based You sample-file.

Did You skip my the last line - a question?

 

[sam20]

Thanks @vletm. I tried your file. It does create the report sheets in a new workbook but the macro fails at this point ".Names(Split(DS, ",")).Delete". It's Run-time error 1004 (Application-defined or object-defined error).
When I used your code in my working file, it also had the same error.
The other thing I also noticed on my working file is that after it creates all report sheets in a new workbook, formulars are still in it except for the "cover" sheet where paste special value worked. For the rest of the sheets, it didn't work

I am attaching another sample book with formulars, but all the formulars have "#REF!" errors. At least this way you could test this workbook for paste special value issue. I had to delete few report sheets in order to upload the file.

I found another macro that might be useful for the name deletion - see below. It keeps Print_Titles" & Print_Area" name ranges and deletes everything else. If this could be incorporated in the macro, this might solve the error issue???

Sub DeleteAllRangesExceptPrintRange()
Dim n As Name
For Each n In ActiveWorkbook.Names
If Right(n.Name, 11) <> "!Print_Area" And n.Name <> "Print_Area" _
And Right(n.Name, 13) <> "!Print_Titles" And n.Name <> "Print_Titles" Then n.Delete
Next n
End Sub

I appreciate all you help so far.

 

[vletm]

sam20
Seems that You have skipped my writings.
> Range ... cannot work, because there are no ranges
> Formulas ... have You written - what would be result of any macro?
> You have named that You would like to get 'Macrosheet' working, instead of modify code - it should work now.
> Do Your original macro work different way?
> Could You send a sample result after 'all has done'?
> What to test more, if I do not know expected result?

I continue checking ... and
... my code needed 'some minor' modifications.
Now,
> it should copy 'selected' sheets to 'new workbook'
> 'new workbook' sheets should have 'only' values
> 'selected' named ranges should be deleted from 'new workbook'
>> Still, I cannot test everything! <<

 

[sam20]

Thanks @vletm . Your new code works . It did everything I wanted. The sample book you attached had only values in the report. So I then copied the code to the SampleBook2 which has formulars but with #REF! . At least this way I was able to test out the paste special value and that worked, no issues, very happy .

Next, I copied the code to my actual working file and delete name range didn't work . I did copy your colored columns and checked name manager in my actual file to see if I was missing any name ranges. The error code was "run-time error 1004" occurred at: "ActiveWorkbook.Names(oo).Delete". It was highlighted in yellow. I am not sure how to fix this. Is it possibly due to different file extension type. Mine is xlsm, just guessing here.

I am attaching SampleBook2 which has your current code (works in this file). I also added another code in module2. This is a modified code from the initial code you provided and some of the current code. I took paste special value part - this worked. I also added a new delete name range code in as well (found online) - this didn't work. The code itself works and therefore I tried to incorporate in....not good doing this.

I would appreciate if you could fix the modified code and that might do the trick on my actual working file.
Can't upload my working file....confidentiality issue plus the file is about 33mb. Fixing the modified macro I what I am hoping that will work in my file.

 

[vletm]

sam20
It's a challenge for me, if I cannot get answers ...
As well as,
if someone writes - It did everything I wanted
But
after that continues writing ... something works ... something works different than it should work
especially, I have not gotten clear answer - what is the needed result?
Your module2-code ... has same name as module1-code ... that would be something different!
... as I've written many times - I cannot test all! ... as well as I don't know - what is the needed result?
Your working file ... You could try to clear all values from those ... add ... one different number pre sheet ... and check its size?

I have tested that 'delete-named-range-name' and it works for me.
After run that macro - there were no more that named range (that range data stayed of course).

 

[sam20]

Thanks @vletm . The needed result is what your current code achieved in SampleBook2 (code sitting in module1 - Sub CreateFile).
But the problem arises when I copy that code and put it in my actual working file is where it fails. The part where it fails is name range deletion as I mention before. So I tried to modify your code and insert a different code for deleting name ranges except for "Print_Titles" & "Print_Area".
That failed for me.

I would like you to look at that code in module2 to see if that can be fixed. I have included the error message in the SampleBook2.

I just want to be clear that you have tested macro in module2 (sub FileCreate) and if this works for you or not?
If you do get the same error, than I would hope you can fix it.

Thanks again @vletm

 

[vletm]

sam20
... hmm? If the expected result is same as Source file ... then ... there no need any code!
... and actually - it cannot be true, because the expected result file is .xlsx-file!
Yes ... You've written few times that then You copy 'my code' to Your file then something won't work with those ranges,
but as I've also written - for me even that part did not give any error! Did You test my newer version? It would give some 'hint' - what?
It's a challenge to fix anything - before can get a challenge = error!
I tested again again again ... 'my code' works ... I added there more features ... and it still works.
SaveAs is/was a little tricky - because You're using Windows ... Okay.
Next Excel's feature is that if use .xlsb-files and wants .xlsx-files then ... error 1004.
I tested to Save that source file as .xlsm ... then even that didn't give any error.
Here both files ... source and ... expected result?

 

[sam20]

Thanks @vletm......update

I figured out how to solve my issue. I used two lines of code; "On Error Resume Next" before the name range deletion code and "On Error GoTo 0" afterwards. This worked on the Samplebook2 and in my actual working file. I was looking online and saw this and figured lets just try it.

I am relieved now that the macro is working. Thank you so very much for all your help. I am very grateful

I will take a look at your recent macro and will test it out. If no issues then I can use either of them.

Thanks again @vletm. Hoping that I don't have to post anything further for this.

 

[sam20]

@vletm.....update #2
It works perfectly in the samplebook2.
I copied that code and pasted in my working file. I get compile error at "RS = Empty". At this point I am not worried about it. It just could be my working file that has some kind of bug or something that doesn't allow it to run as in the samplebook2.
I can still use the code for others if I need it. I know it works in the samplebook2 so it should work in a different file.
Anyways......thank you again.

 

[vletm]

sam20
case RS = Empty
Do You have somewhere Option Explicit -text?
If so, then You should declare RS as You have done with other Your variables.

 

[sam20]

@vletm
Yes, I did have "Option Explicit" text in the macro above. I just added a new module and copied the code in that. Now it works without any issue.
Thank you so much.