CODEWITHSUNDEEP
vbaexcelcopy-paste
user2172916
user2172916

Reputation: 51

copy paste range in first empty column in excel vba

I want to copy the range of cells (C1:Z1000) of worksheet3 and paste them to the first empty column of worksheet1 (in row 1). The code below blocks at the last line: source.Range("C1:Z1000").Copy destination.Cells(1, emptyColumn)

Sub CopyRange()

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet3")
Set destination = Sheets("Sheet1")

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

source.Range("C1:Z1000").Copy destination.Cells(1, emptyColumn)

End Sub

Upvotes: 1

Views: 37431

Answers (4)

user3777983
user3777983

Reputation: 1

I found this post, modified it to fit my needs. Will paste transpose

Sub Transpose_PasteModified()

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet2")
'Data Source
source.Range("A3:C3").Copy
'Gets Empty Column
emptyColumn = destination.Cells(3, destination.Columns.Count).End(xlToLeft).Column

'In order to avoid issues of first row returning 1
'in this case #3 is the row so we're checking A3
'If row changes then change A3 to correct row

If IsEmpty(destination.Range("A3")) Then
destination.Cells(3, 1).PasteSpecial Transpose:=True
Else
emptyColumn = emptyColumn + 1
destination.Cells(3, emptyColumn).PasteSpecial Transpose:=True
End If

End Sub

Upvotes: 0

StoriKnow
StoriKnow

Reputation: 5867

I think your issue was the way you were obtaining the emptyColumn value, as others suggested. This works for me:

Sub CopyRange()

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet3")
Set destination = Sheets("Sheet1")

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

source.Range("C1:Z1000").Copy destination.Cells(1, emptyColumn)

End Sub

The way you currently have it will pull the very last column in the worksheet, which seems to thrown an error when pasting to it. The above approach will pull the very first empty column. That is, if column C is empty, the value of emptyColumn will be 3

Upvotes: 2

Olle Sjögren
Olle Sjögren

Reputation: 5385

Have you tried stepping through your code?

If you do, you'll notice that the following line will always set the emptyColumns variable to the far right column, regardless of which columns are used:

emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlUp).Column

By adding 1 to it and pasting you try to paste to a column that does not exist. That will give you an error every time.

Instead, try the following to find the last used column. It searches from the far right column in the first row and goes left (like typing CTRL+LEFT), in order to find the last used column:

emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlToLeft).Column

Then you can add 1 to it and paste.

Upvotes: 1

Our Man in Bananas
Our Man in Bananas

Reputation: 5979

try this:

emptyColumn = destination.Cells(1, destination.Columns.Count).End(xltoright).Column

source.Range("C1:Z1000").Copy Destination:= Cells(1, emptyColumn)

named arguments are followed by a colon equals :=

your code for End should be: End(xlToRight)

another alternative would be:

source.Range("C1:Z1000").Copy 
with destination
    .cells(1,emptycolumn).select
    .paste
end with

I hope that helps

Philip

Upvotes: 0

Related Questions