Copy/Paste special an assortment of rows

[anastabation]

I am new to this coding caper and I am desperate to learn.

I've got an excel sheet that has a whole bunch of data and it changes year to year. What I need to do is copy, paste special the previous years data, then change the variables to come up with current years data and then compare figures.

The problem is that the data is in a number of rows. Currently my code looks like this.

Range("C7:T7").Copy
    Range("AA7").PasteSpecial Paste:=xlValues
Range("C10:t10").Copy
    Range("AA10").PasteSpecial Paste:=xlValues
Range("C13:T13").Copy
    Range("AA13").PasteSpecial Paste:=xlValues
Range("C16:t16").Copy
    Range("AA16").PasteSpecial Paste:=xlValues
Range("C19:t19").Copy
    Range("AA19").PasteSpecial Paste:=xlValues
Range("C24:t24").Copy
    Range("AA24").PasteSpecial Paste:=xlValues
Range("C27:t27").Copy
    Range("AA27").PasteSpecial Paste:=xlValues
Range("C30:T30").Copy
    Range("AA30").PasteSpecial Paste:=xlValues
Range("C33:T33").Copy
    Range("AA33").PasteSpecial Paste:=xlValues
Range("C36:T36").Copy
    Range("AA36").PasteSpecial Paste:=xlValues
Range("C39:T39").Copy
    Range("AA39").PasteSpecial Paste:=xlValues
Range("C42:T42").Copy
    Range("AA42").PasteSpecial Paste:=xlValues
Range("C45:T45").Copy
    Range("AA45").PasteSpecial Paste:=xlValues
Range("C48:T48").Copy
    Range("AA48").PasteSpecial Paste:=xlValue
Range("C51:t51").Copy
    Range("AA51").PasteSpecial Paste:=xlValues
Range("C54:T54").Copy
    Range("AA54").PasteSpecial Paste:=xlValues
Range("C57:T57").Copy
    Range("AA57").PasteSpecial Paste:=xlValues
Range("C60:T60").Copy
    Range("AA60").PasteSpecial Paste:=xlValues
Range("C63:T63").Copy
    Range("AA63").PasteSpecial Paste:=xlValues
Range("C66:T66").Copy
    Range("AA66").PasteSpecial Paste:=xlValues
Range("C69:t69").Copy
    Range("AA69").PasteSpecial Paste:=xlValues
Range("C72:t72").Copy
    Range("AA72").PasteSpecial Paste:=xlValues
Range("C75:t75").Copy
    Range("AA75").PasteSpecial Paste:=xlValues
Range("C78:t78").Copy
    Range("AA78").PasteSpecial Paste:=xlValues
Range("C81:T81").Copy
    Range("AA81").PasteSpecial Paste:=xlValues
Range("C84:T84").Copy
    Range("AA84").PasteSpecial Paste:=xlValues
Range("C97:T97").Copy
    Range("AA97").PasteSpecial Paste:=xlValues
Range("C100:T100").Copy
    Range("AA100").PasteSpecial Paste:=xlValues
Range("C106:T106").Copy
    Range("AA106").PasteSpecial Paste:=xlValues
Range("C109:T109").Copy
    Range("AA109").PasteSpecial Paste:=xlValues
Range("C112:t112").Copy
    Range("AA112").PasteSpecial Paste:=xlValues
Range("C118:t118").Copy
    Range("AA118").PasteSpecial Paste:=xlValues
Range("C121:t121").Copy
    Range("AA121").PasteSpecial Paste:=xlValues

It doesn't run too slowly but it is very long and very messy. It also doesn't allow the flexibility to alter the spreadsheet.

I've tried playing around with loops but had no success. I've done a sample worksheet to show waht I want but don't know how to attach files.

Any help would be greatly appreciated.

 

[vconfused]

If you add Application.ScreenUpdating = False to the beginning and Application.ScreenUpdating = True to the end, it may speed it up a bit. You could loop if you had, say a column that consisted of nothing but "X"'s -- if there was an X, then the code would copy that row. It wouldn't speed up, but it would allow you to insert/remove rows without having to modify the code...

 

[SydneyGeek]

Are these cells in C:T the only formulas, or are there formulas in the other rows too?

Denis

 

[SydneyGeek]

If the only formulas in C:T are for the cells you want to transfer--

Sub Macro3()
    Dim c As Range
    
    Application.ScreenUpdating = False
    For Each c In Columns("C:T").SpecialCells(xlCellTypeFormulas, 23)
        c.Offset(0, 24).Value = c.Value
    Next c
    Application.ScreenUpdating = True
End Sub

Denis

 

[anastabation]

Denis you are a legend! Thanks heaps the code. It works great.

The problem is there are formulas/data in the other rows that I don't want to transfer over. I mean I could but as it's a summary worksheet, I don't want it to look too messy.

I've been reading Mr Excel's book on "VBA Macros for Microsoft Excel" and I think the ideal solution would be say put a letter like "T" in front of all the rows I want copied over and perform and "If Then" Loop. Unfortunately I've only just started learning VBA so I don't know how to code that.

 

[SydneyGeek]

Let's say that column B has the T markers. Try this:

Sub FilterCopy()
    Dim rwLast As Long
    Dim c As Range
    Dim Rng As Range
    
    'find the last used row in column B
    'this is the column with the T markers
    rwLast = Cells(Rows.Count, 2).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    ActiveSheet.AutoFilterMode = False
    Set Rng = Range("B1:T" & rwLast)
    With Rng
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="T"
    End With
    For Each c In Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible)
        If c.Column > 2 Then c.Offset(0, 24).Value = c.Value
    Next c
    Rng.AutoFilter
    Application.ScreenUpdating = True
End Sub

Denis

 

[anastabation]

Thanks Denis! It works a treat!