Change cell number/reference, but not sheet name in a macro

[Hambone81]

Hi, first post. Need to change the cell number in a macro, but not the sheet name. For example, I recorded a macro for the action I want- changing formula to Sheet1!C5 to Sheet1!C4 and Sheet1!C43 to Sheet1C42 (see resulting code below). But what if I want to run this in cell that pulls from a different sheet with the same cell reference changes? Such as changing Sheet2!C5 to Sheet2!C4 and Sheet2!C43 to Sheet2!C42? I have to do this every quarter in about 500 cells, so I'm trying to find a faster way. I'm an excel newbie so take it easy on me. Thanks for all your help.

Original recorded macro:

Sub Macro3()
'
' Macro3 Macro
'


'
ActiveCell.FormulaR1C1 = "=sheet1!R4C3"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=sheet1!R42C3"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

 

[farmerscott]

Hambone,

welcome to the forum.....

VBA has the ability to loop thru sheets to collect data.

So would you like a formula in the cells or just a value?

You can rerun the macro/VBA code if you make changes and need to update your data.

FarmerScott

 

[Hambone81]

Thanks for the reply. Just need the value the formula pulls. Basically I have a presentation-grade summary table on one tab that pulls raw quarterly real estate data from different tabs, i.e. sheet1, sheet2, etc.... Each row reference corresponds to a different quarter, C4 is Q4 2013, C5 is Q3 2013,etc.... The columns correspond to different types of data, column B is inventory, column C is rental rate, etc... (FYI there are 50 different types of data). So, if someone wants a report for Q3 2013 data one day and then another person wants Q4 2013 data the next, I don't have to edit each formula manually to extract the data from different quarters in the summary table. I basically have no VBA knowledge so it would be easier if I could just run a simple macro. I might be able to use the OFFSET function, but I wanted to see if there was a macro I could run.

Hambone,

welcome to the forum.....

VBA has the ability to loop thru sheets to collect data.

So would you like a formula in the cells or just a value?

You can rerun the macro/VBA code if you make changes and need to update your data.

FarmerScott

 

[farmerscott]

Hambone,

1. so each sheet represents a different year???
2. within each sheet there is data relating to each qtr you want to extract??


How do you intent to run the sub for each qtr? That is, will you want all years (collect data from all the tabs) for a particular qtr, or sometimes just one year?

Do you have any ideas on how you are going to input this data for the sub (input box, check box)?

FarmerScott