Loop to save all versions of report using drop down

Hello and thank you for any help I can get with this!

I am trying to use the following Macro to save all versions of a report, but I don't know how to create the loop. The reports value change based off of a drop down cell and I want to save every version of that drop down to a designated folder. My current Marco is causing the saved files to crash excel and I am not sure why.

My drop down is in cell C2 and it is the data validation is based on O16:O159

I am copy and pasting several tabs to a new worksheet because I need to save the report as values since I am currently using a cube connection that is not accessible by the end user.

Any help on this would be greatly appreciated!

This is the current Macro I am using:

Please Login or Register  to view this content.

Hi and welcome to the forum.

1.) You need to edit your post and add CODE tags around your VBA code. It's a forum rule, and others are adamant about enforcing it.

2.) "My current Marco is causing the saved files to crash excel"
Can you better explain exactly what happens? It can help diagnose the issue. If the code errors, what is the error description and what line of code is highlighted when you click the Debug button on the error dialog?

3.) Delete the two Call commands.

Please Login or Register  to view this content.

2.) Describe "crash". What do you see; Does it lockup, does it exit excel?

3.) Remove the parenthesis
.Range("a1").PasteSpecial(Paste:=xlValues)

Try this (not tested).

Please Login or Register  to view this content.

That actually worked?

Need to step away for a while. I'll take a look at looping in a couple of hours.

The code (both old and new) doesn't use the value in the C2 DV list. So what changes in the code for the loop?

What file name do you want to save each version in the loop?

Please Login or Register  to view this content.