VBA for Copy and Paste

[mvatoi]

Hi all,

I have a large pivot table with a drop down button of different period 1, 2, 3....

In every period, I link some of data from the pivot to a regular table on the same sheet (E2:H2), then copy this data and paste it to a third table

I have 3 tables:
1) pivot table is in Colum A to D,
2) a regular table with formula in E2:H2 (This second table links to data from the pivot table, every time the pivot period change, this data change, I need to copy/paste this data to the third table, then change period, and copy this same range again, but then it will have different data)
3) another regular table (no formula in cells) E4:H10

======================
I want to:

step1= Choose period 1 in pivot table
step2= Copy E2:H2
step3= Paste Special to cell E4
step4= Choose period 2 in pivot table
step5= Copy E2:H2
step6= Paste Special to Cell E5
choose period 3 in pivot table
repeat steps..... and so on until the last period is chosen

I have the macro listed below, but the problem is there's a new period each week, and I have to go back and edit the macro to add the new period.

I want to say something like:

Dim pd As Period

choose Period "1" in Pivot Table
Copy cell E2:H2
Paste Special to cell E4
Pivot Tables
next pd

But I'm sure how to write it, I'm not good at VBA at all.

Thanks all, and below are the macro I'm using now

===========================
ActiveSheet.PivotTables("PivotTable1").PivotFields("WorkPeriod"). _
CurrentPage = "1"
Range("E2:H2").Select
Application.CutCopyMode = False
Selection.Copy
Range("E4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.PivotTables("PivotTable1").PivotFields("WorkPeriod"). _
CurrentPage = "2"
Range("E2:H2").Select
Application.CutCopyMode = False
Selection.Copy
Range("E5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

and so on.....

 

[xenou]

Does this help?

Public Sub test()
lastPeriod = Application.InputBox("What is the last period?", Type:=1)

For x = 1 To CInt(lastPeriod)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("WorkPeriod"). _
        CurrentPage = CString(x) 'Instead of "1" and incrementing from there
    Range("E2:H2").Copy
    Range("E" & 3 + x).PasteSpecial XlPasteType = xlPasteValues  'Instead of E4 and incrementing from there
Next x

End Sub

Public Sub test2()
lastPeriod = Application.InputBox("What is the last period?", Type:=1)

For x = 1 To CInt(lastPeriod)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("WorkPeriod"). _
        CurrentPage = x 'Instead of "1"
    Range("E2:H2").Copy
    Range("E" & 3 + x).PasteSpecial XlPasteType = xlPasteValues  'Instead of E4
Next x

End Sub

The first version uses a string for the periods, and the second a number. Looks like your recorded code wants a string so I tried that first. BTW, this code is edited to remove unnecessary steps and unnecessary defaults...the macro recorder is useful but prolix.