VBA Copy/Paste Range Macro HELP!!

[flicky_longcartridge]

Hi Excel Experts!!

I am attempting to write a macro to do the following.....

1) Select all the data on the current sheet (HIST). Data spans from column A:H. I will call this Selection 1.
2) Paste Selection 1 Underneath the last row of selected data. (Creating Selection 2.)
3) Change the value of the 1st cell in column F (at the beginning of selection 2) and then autofill this value all the way to the last row of Selection 2.
4) Paste Selection 1 Underneath the last row of Selection 2 (Creating Selection 3.)
5) Change the value of the 1st cell in column F (at the beginning of Selection 3) and then autofill this value all the way to the last row of selection 3.

The code below works for a fixed selection size. Unfortunately the number of rows of data changes week on week and therefore I need to exchange the fixed values to deal with the variables....

Range("A1:H169").Select
'This range size will vary'
Selection.Copy
Range("A170").Select
'This needs to be the line below the range size above'
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Range("F170").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VALUE(2)"
'This value will always be the same'
Range("F170").Select
Selection.AutoFill Destination:=Range("F170:F338")
'This copies the Value 2 down to the bottom of the range I have just pasted'
Range("A339").Select
'This needs to be the line below the 1st range I pasted'
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Range("F339").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VALUE(6)"
'This value will always be the same'
Range("F339").Select
Selection.AutoFill Destination:=Range("F339:F507")
'This copies down the Value 6 to the bottom of the 2nd range I have just pasted'

N.B: All comments apply to the line of code above...

Thanks for your help!!!

 

[onlyadrafter]

Hello,

is this working as expected?

Sub FLICKY()
    For MY_REPEATS = 2 To 6 Step 4
    MY_LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1:H" & MY_LR).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Range("F" & MY_LR + 1).FormulaR1C1 = "=value(" & MY_REPEATS & ")"
    Range("F" & MY_LR + 1).AutoFill Range("F" & MY_LR + 1, "F" & Range("F" & Rows.Count).End(xlUp).Row)
    Next MY_REPEATS
End Sub

 

[flicky_longcartridge]

Thats almost working...

Its just doing the third selection twice!!!

Could you tweak it for me?

Thanks for your help!!

 

[flicky_longcartridge]

If you break down what the code is doing... I might be able to tell you where it's going wrong?!!!

 

[onlyadrafter]

Hello,

figured it out, you want the original rows copied twice. My code copied the original rows once, then copied the original and the first copied rows. HAve changed the code to suit.

Sub FLICKY()
    MY_LR = Range("H" & Rows.Count).End(xlUp).Row
    For MY_REPEATS = 2 To 6 Step 4
        MY_NEW_LR = Range("H" & Rows.Count).End(xlUp).Row
        Range("A1:H" & MY_LR).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Range("F" & MY_NEW_LR + 1).FormulaR1C1 = "=value(" & MY_REPEATS & ")"
        Range("F" & MY_NEW_LR + 1).AutoFill Range("F" & MY_NEW_LR + 1, "F" & Range("F" & Rows.Count).End(xlUp).Row)
    Next MY_REPEATS
End Sub