Forum Discussion
Excel VBA Macro inadvertantly renaming Power Query queries
This is for anyone that might care about this solution. After a lot of debugging and refactoring of this code I think I'm on the right path. I'm changing any time I copy a sheet that is based on query results from VBA code that looks like this...
ForReview.Copy After:=wbCreated.Sheets(2)
To looking something like this instead...
wbCreated.Sheets.Add.Name = "For Electronic Review"
Set rngToCopy = wbMain.Sheets("For Review").Range("A1").CurrentRegion
Set rngToPaste = wbCreated.Sheets("For Electronic Review").Range(rngToCopy.Address)
rngToCopy.Copy
rngToPaste.PasteSpecial Paste:=xlPasteValues
rngToPaste.PasteSpecial Paste:=xlPasteFormats
Call FormatForReview(wbCreated.Sheets("For Electronic Review"))
(There are several output worksheets in various output workbooks that have very specific formatting that I need to recreate in areas.)
The downsides of this approach are...
1. There are more steps to the copy and despite everything being already formatted in the source worksheet not all of it (like images that have been added) get carried over like before so sometimes I need additional formatting steps after the PasteValues/PasteFormats.
2. I have to recreate any table declarations and reformat them where necessary because the PasteFormats doesn't get me that either.
The upsides are...
1. My query names aren't getting clobbered during the workbook creation
2. I don't have to loop through the Destination workbook afterwards and remove queries and named ranges that inadvertently got copied over during the previous method of worksheet copy.
Overall, I still don't have it done, but I'm at least closer to solving this problem.
- pelowski4 years ago
Helper III
I thought this was on the right path the solving the issue, but unfortunately I'm running into other issues now including queries not existing the next time I try to do a refresh. I'm really loathing VBA and query refreshing right now.