Copying Columns from One Sheet to Another

[rossmiddleto]

Hi All,

I get an error when i try and run the below code as follows:

"Select method of range class failed"

Any ideas what I am doing wrong?



Sheets("sheet1").Range("c:c").Select
Selection.Copy
Sheets("sheet3").Range("A3").Offset(0, 1).EntireColumn.Select
Selection.Paste

[Sean.DiSanti]

Do Columns("c:c") instead of range

[HaHoBe]

Hi, rossmiddleto,

if you start with row 3 for a paste of a column there won´t be enough cells left to paste a whole column to.

Either use

Sheets("sheet1").Columns("C:C").Copy
Sheets("Sheet3").Columns("B:B").Paste for a whole column or
Sheets("sheet1").UsedRange.Columns("C:C").Copy Destination:=Sheets("sheet3").Range("B3") to limit the copying only to the used range (starting off in column A) or
With Sheets("sheet1")
.Range("C1:C" & .Cells(Rows.Count, "C").End(xlUp).Row).Copy Destination:=Sheets("sheet3").Range("B3")
End With Ciao,
Holger

[rossmiddleto]

Thank you both kindly fror your help.

HaHoBe, I have used your last example and it works perfectly. If i want to paste the range of cells into the first available empty column on sheet B, how would I change the code?

I have tried the foloowing with no luck:


With Sheets("sheet1")
.Range("C1:C" & .Cells(Rows.Count, "C").End(xlUp).Row).Copy Destination:=Sheets("sheet3").Range("A3").End(xltoRight).Offset(0, 1)
End With

[HaHoBe]

Hi,

I´d preferred the Cells-Object to the Range-Object and worked my way from the far right side of the sheet:

Dim ws1 As Worksheet
Dim ws3 As Worksheet
'...
Set ws1 = Sheets("Sheet1")
Set ws3 = Sheets("Sheet3")
With ws1
.Range("C1:C" & .Cells(Rows.Count, "C").End(xlUp).Row).Copy _
Destination:=ws3.Cells(3, ws3.Cells(3, Columns.Count).End(xlToLeft).Column + 1)
End With
'...
Set ws3 = Nothing
Set ws1 = Nothing Ciao,
Holger