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
1
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!