Selecting cell using range.offset
 

I am trying to take 4 columns from one spreadsheet and paste it to th
bottom of another. I am doing each one seperately. The first on
worked fine. The second one did not because I need to save th
location of the bottom of the sheet and then reference that with a
offset of 1,1 to move it to the next row, next column. When I do tha
I get run-time error '1004' - method 'range' of object '_global
failed.

Here is my code:

Dim bottomofsheet As Long

Sheets("tracerfinal").Select
Range("E1:E" & Range("A65536").End(xlUp).Row).Copy
Sheets("Final balancing").Select
bottomofsheet = Sheets("Fina
balancing").Range("A65536").End(xlUp).Row
[A65536].End(xlUp).Offset(1).Select
ActiveSheet.Paste


Sheets("tracerfinal").Select
Range("D1:D" & Range("A65536").End(xlUp).Row).Copy
Sheets("Final balancing").Select
Range(bottomofsheet, 1).Offset(1, 1).Select
ActiveSheet.Paste

The error happens on the
Range(bottomofsheet, 1).Offset(1, 1).Select line.

I have tried several variations including changing the dim from Long t
object to range and then modifying accordingly.

Any help would be greatly appreciated. Thanks

--
Message posted from http://www.ExcelForum.com

Selecting cell using range.offset
 

Looks like you want to put the copies columns side by side, but in a
different order.

With Worksheets("TracerFinal")
set rngE = .Range(.Range("E1"),.Cells(rows.count,5).End(xlup) )
set rngD = .Range(.Range("D1"),.Cells(rows.count,4).End(xlup) )
End With
With Worksheets("Final balancing")
set rng1 = .Cells(rows.count,1).End(xlup)(2)
End With
rngE.copy Destination:=rng1
rngD.copy Destination:=rng1.offset(0,1)

if rngD will be the same number of rows as rngE then

With Worksheets("TracerFinal")
set rngE = .Range(.Range("E1"),.Cells(rows.count,5).End(xlup) )
End With
With Worksheets("Final balancing")
set rng1 = .Cells(rows.count,1).End(xlup)(2)
End With
rngE.copy Destination:=rng1
rngE.Offset(0,-1).copy Destination:=rng1.offset(0,1)

--
Regards,
Tom Ogilvy


"presence76 " wrote in message
...
I am trying to take 4 columns from one spreadsheet and paste it to the
bottom of another. I am doing each one seperately. The first one
worked fine. The second one did not because I need to save the
location of the bottom of the sheet and then reference that with an
offset of 1,1 to move it to the next row, next column. When I do that
I get run-time error '1004' - method 'range' of object '_global'
failed.

Here is my code:

Dim bottomofsheet As Long

Sheets("tracerfinal").Select
Range("E1:E" & Range("A65536").End(xlUp).Row).Copy
Sheets("Final balancing").Select
bottomofsheet = Sheets("Final
balancing").Range("A65536").End(xlUp).Row
[A65536].End(xlUp).Offset(1).Select
ActiveSheet.Paste


Sheets("tracerfinal").Select
Range("D1:D" & Range("A65536").End(xlUp).Row).Copy
Sheets("Final balancing").Select
Range(bottomofsheet, 1).Offset(1, 1).Select
ActiveSheet.Paste

The error happens on the
Range(bottomofsheet, 1).Offset(1, 1).Select line.

I have tried several variations including changing the dim from Long to
object to range and then modifying accordingly.

Any help would be greatly appreciated. Thanks.


---
Message posted from http://www.ExcelForum.com/

Selecting cell using range.offset
 

Thanks alot. It worked great

--
Message posted from http://www.ExcelForum.com