Copy/paste values in column if condition is met

[tjur]

Hi there - I'm brand-new to VBA (as in the last 3 days) and I've been asked to take values from a pivot table and paste the values into a column in another sheet if the column title on the other sheet matches the prior month from today's date. The intent is for someone to update the pivot table and 'lock' the values in another sheet with the click of a button.

In the example I've attached, the March data is currently populated with Vlookups from the pivot table. Can I use VBA to copy/paste just these cells based on matching the month, or is there a better way to move the pivot table values to the new spreadsheet without Vlookups? So far, all of the code examples I've found online have all been about searching through rows to find matches and then copying the entire row to a new sheet.

Thank you for any help you can provide.

Sample Workbook.xlsx

[p45cal]

Try:

Sub blah()
Set Rng = Sheets("Sheet1").Range("B2").CurrentRegion
Set Rng = Intersect(Rng, Rng.Offset(2, 1))
With Rng.Columns([MATCH('Calc Sheet'!I2,Sheet1!B2:M2,0)])
  .FormulaR1C1 = "=GETPIVOTDATA(""Number"",'Calc Sheet'!R6C12,""Location"",RC1)"
  .Value = .Value
End With
End Sub

[tjur]

That works perfectly! Thank you so much.