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

Closed
micksv Posts 2 Registration date Sunday July 26, 2015 Status Member Last seen July 26, 2015 - Jul 26, 2015 at 04:42 PM
micksv Posts 2 Registration date Sunday July 26, 2015 Status Member Last seen July 26, 2015 - Jul 26, 2015 at 11:01 PM
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 response

micksv Posts 2 Registration date Sunday July 26, 2015 Status Member Last seen July 26, 2015 1
Jul 26, 2015 at 11:01 PM
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.
1