Excel VBA - Copy Column and Paste Values to a New Column in a New Worksheet Every Time Macro Is Initiated

Question

I currently have a spreadsheet that calculates variances in columns D and E. At the end of the month I would like to copy and paste the values into another columns in another worksheets (D copy values in "Historical loan count", E - in "Historical UPB) to keep track of the variances month to month.

I have developed a macro that copies the contents that need to be copied from columns D and E to columns N. But What I really need is that each time I run the macro it to copy the values from columns D and E and paste it into a new next column in other worksheets.

I found a similar question here (Copy and Paste data inside one workbook). The problem was resolved with using Offset property but, if I understand right, I can't apply Offset with different worksheets.

Sheets("Forecast Tableau format").Select
Range("D3:D6").Select
Selection.Copy
Sheets("Historical Loan Count").Select
Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Forecast Tableau format").Select
Range("D7:D10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical Loan Count").Select
Range("N9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.SmallScroll Down:=6

Sheets("Forecast Tableau format").Select
Range("D11:D14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical Loan Count").Select
Range("N16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Forecast Tableau format").Select
Range("D15:D18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical Loan Count").Select
Range("N23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Forecast Tableau format").Select
Range("D19:D22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical Loan Count").Select
ActiveWindow.SmallScroll Down:=3
Range("N30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("Forecast Tableau format").Select
Range("E3:E6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical UPB").Select
ActiveWindow.SmallScroll Down:=-3
Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("Forecast Tableau format").Select
Range("E7:E10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical UPB").Select
Range("N9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("Forecast Tableau format").Select
Range("E11:E14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical UPB").Select
Range("N16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.SmallScroll Down:=3
Sheets("Forecast Tableau format").Select
Range("E15:E18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical UPB").Select
Range("N23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveWindow.SmallScroll Down:=6
Sheets("Forecast Tableau format").Select
Range("E19:E22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Historical UPB").Select
Range("N30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

Sounds like your problem is really: [How to find the last cell/row/column](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). If you can use that method(s) to identify the *last* column in the destination worksheets, then you can use Offset (or +1) to get a handle on the *next* column, and then paste values accordingly. — David Zemens, May 07 '18 at 19:25

Excel VBA - Copy Column and Paste Values to a New Column in a New Worksheet Every Time Macro Is Initiated

0 Answers0