Hey so I am having this problem with this macro I recorded. I am working with a workbook with multiple worksheets in it, one particular worksheet uses simple addition and subtraction formulas to get the differences between two numbers. These formulas are all the same and are already input in all the cells within the sheet. The formulas reference numbers from other sheets within the workbook to find the difference. So each month has its own cash flows, then the last sheet where the difference is calculated is the variance between the two months. I decided to record the macro as I inputted formulas and dragged it across the worksheet and making sure I had the "relative referencing" button on. After I recorded the macro, I changed the sheet name that the formulas referenced to see what would happen, as I expected it didn't calculate the numbers correctly since the sheet name was now different. I want to solve this issue since I plan on using this macro on multiple workbooks with the same format but difference data and sheet names, but not sure how to get around the issue of- the sheet names being different from what are in the formulas to reference.
0
votes
by the way I am extremely new to VBA and writing Macros, (like a couple days into it) so if this is a simple fix i really wouldn't know about it. thank you
– Peter Mossad
Try using the macro recorder as a starting point
– Tom
Go into your recorded macro and replace wherever you see your old sheet name to the new sheet name.
– dwirony
well either changed the names back, or learn how to edit the text strings within the cells (basically learning how to edit the formulas programmatically)
– Doug Coats
I really was trying to automate this process completely without going in the code again, and sorry can you elaborate tom? by the way is there anything called "sheet.offset" i can use to get around the issue?
– Peter Mossad
1 Answers
0
votes
One possible solution would be to name the worksheet "objects".
Worksheets in a workbook are objects that can be Named via the VBA editor properties window. This is different than the display name that appears at the bottom in the grid view.
For example, instead of using Sheets("Page_A").Range("A1").value you could use PAGE_A.Range("A1").value
The implication is that if the "display" name of the worksheet changes, the code continues to reference the "object" name of the worksheet. This can work so long as worksheets are not deleted.
In the example images, the code to change cell A1 would be
WkshtObjectName.range("A1").Value = "Hello"
You may be able to use this technique to get around the changing worksheet names.
