VBA Copy and paste range to next empty cell(s) to right [Closed]

Report
Posts
2
Registration date
Sunday July 26, 2015
Status
Member
Last seen
July 26, 2015
-
Posts
2
Registration date
Sunday July 26, 2015
Status
Member
Last seen
July 26, 2015
-
Hello! I'm fairly new to VBA, and I'm new here, so please bear with me as I try to explain what I need. I have a set of data that will be updated on a regular basis. When the data is updated, I need my macro to copy the updated range (formulas) and paste them into another tab as values. I am copying F4:F11 from "Test Cart" and pasting them into B4:B11 in "Calculations." I need each subsequent paste to be in the next cell to the right (C5:C11, then D5:D11, etc.). I have tried so many things... But my current macro looks like this:

Sub Calculate()
'
' Calculate Macro
'
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Test Cart")
Set destination = Sheets("Calculations")

'find empty Column (actually cell in Row 4)'
emptyColumn = destination.Cells(4, destination.Columns.Count).End(xlToLeft).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If

Sheets("Test Cart").Range("F4:F11").Copy
Sheets("Calculations").Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


source.Range("F4:F11").Copy destination.Cells(4, emptyColumn)

Application.CutCopyMode = False


End Sub

Any help would be appreciated!

Thanks.

1 reply

Posts
2
Registration date
Sunday July 26, 2015
Status
Member
Last seen
July 26, 2015

I fixed it! Here is the working macro:

Sub Calculate()
'
' Calculate Macro
'
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Test Cart")
Set destination = Sheets("Calculations")

'find empty Column (actually cell in Row 4)'
emptyColumn = destination.Cells(4, destination.Columns.Count).End(xlToLeft).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If

Sheets("Test Cart").Range("F4:F11").Copy

Sheets("Calculations").Cells(4, emptyColumn).PasteSpecial Paste:=xlPasteValues




End Sub

Thanks.