Excel Macro - Repeat Macro Code But Applied to Different Columns

Question

I'm fairly new to VBA and would like to seek some advice.

I'm running a macro to compile tables into a row-by-row transaction list.

However, I do not want to replicate this code 11 times for the different month columns.

How can I perform a loop so it automatically performs the task across all 12 months?

Code below and table conversion as per attached picture.

Thanks!

'For Apr'20 - 'Copy name and category to clipboard
    
    Sheets("Sheet2").Range("A3:C15").Copy
    
'Select last row of reference cell

    lastRow = Sheets("Consolidated Cost Sheet_Working").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Range("A" & lastRow).Select

'Paste clipboard as values

    ActiveCell.PasteSpecial xlPasteValues
    
'Copy period and drag down

    Sheets("Sheet2").Range("D2").Copy
    lastRow = Sheets("Consolidated Cost Sheet_Working").Cells(Rows.Count, "D").End(xlUp).Row + 1
    Range("D" & lastRow).Select
    ActiveCell.PasteSpecial xlPasteValues
    
    Sheets("Consolidated Cost Sheet_Working").Range("C3").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Application.CutCopyMode = False
    Selection.FillDown
    
'Copy Financial Info to Sheet

    Sheets("Sheet2").Range("D3:D15").Copy
    lastRow = Sheets("Consolidated Cost Sheet_Working").Cells(Rows.Count, "E").End(xlUp).Row + 1
    Range("E" & lastRow).Select
    ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats

Conversion Table Concept

score 1 · Accepted Answer · answered May 05 '21 at 03:21

It looks like you recorded a macro and now you want to generalize the functionality. That's how a lot of us start. Your recorded macro looks like this

Sub Macro1()

    ' Lots of code

End Sub

The fastest way you can achieve your objective is to alter the original macro to pass the column in as an argument.

Sub Macro1(Byval col as String)

    ' Lots of code

End Sub

Then replace every instance of column "D" in the macro with column col. For example:

    Sheets("Sheet2").Range(col & "2").Copy
    lastRow = Sheets("Consolidated Cost Sheet_Working").Cells(Rows.Count, col).End(xlUp).Row + 1
    Range(col & lastRow).Select
    ActiveCell.PasteSpecial xlPasteValues

Then you can call the macro repeatedly from another sub, passing in a different column each time.

Sub OtherSub()

    Macro1 "D"
    Macro1 "E"
    Macro1 "F"
    Macro1 "G"

End Sub

There are any number of ways this code can be improved (I particularly recommend you read Some examples of how to avoid select), but this should get you started.

Thanks a lot for the advice! Yes, i recorded some, googled some, and its a whole lot of patchwork. I'll give your solution a go and really appreciate your advice :) — M.Feroz., May 05 '21 at 03:49
Hi Nicholas, what code can I use for the following line, in order to loop it to different Columns i.e. "E", "F" etc: Sheets("Sheet2").Range("D3:D15").Copy — M.Feroz., May 05 '21 at 14:25

Excel Macro - Repeat Macro Code But Applied to Different Columns

1 Answers1