Pasting formula to multiple sheets and then hard coding the #s

Hi there,

How do write a vba such that I can copy and paste a formula onto the last row of multiple sheets and then hard code it? The formulas are unique in each sheet.

I have attached an example excel.

I get new data every month and slip it in as a new tab ie "202102" is the latest Feb data. An index indirect match formula is then used on each sheet to parse the latest data and then I hard code them. I currently do this manually

Possibly...

Please Login or Register  to view this content.

Thanks Dangelor. Pardon me for asking but which part of the code does it know to source cell B1 as the formula to copy? And which part of the code hardcodes the numbers?

Ah just saw that this uses a used range which doesn't work for me as I need to use the indirect index match formula on cell B1 of every worksheet..

I'm using a loop like this so far but sadly the formula pasted is fixed on finding the match with B3 and won't know to use C3

Sub CopyPasteFormula()

Dim sht as Worksheet
Dim My Formula as String

For Each sht in ThisWorkbook.Worksheets

My Formula=sht.Range("B1").Formula
Select Case sht.Name
Case "202102"
Case Else
sht.Range("B" & sht.Rows.Count).End(xlUp).Offset(1).Formula=MyFormula
sht.Range("C" & sht.Rows.Count).End(xlUp).Offset(1).Formula=MyFormula
End Select
Next sht
End Sub

which part of the code does it know to source cell B1 as the formula to copy?

The code doesn't use the formula.

And which part of the code hardcodes the numbers?

Please Login or Register  to view this content.