Copy and paste between sheets error

[carlos_ray86]

I am having a little problem with this code. It won't copy and paste
from one worksheet to another. I have used the same code for copy and
paste before but never in a find loop so I'm guessing maybe I have to
do something different. I also tried

Workbooks("Book1.xls").Worksheets(2).Range(Cells(y, 1), Cells(y,
2)).PasteSpecial Paste:=xlPasteAll

Worksheets(2).Range(Cells(y, 1), Cells(y, 2)).PasteSpecial
Paste:=xlPasteAll

Worksheets("SatLog").Range(Cells(y, 1), Cells(y, 2)).PasteSpecial
Paste:=xlPasteAll

None worked

With Worksheets(1).Range("A1:A500")
Set c = .Find("Substrate # 1", LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
y = 1
Do

Range(c.Offset(0, 0), c.Offset(3, 1)).Copy
Workbooks("Book1.xls").Worksheets("SatLog").Range(Cells(y,
1), Cells(y, 2)).PasteSpecial Paste:=xlPasteAll <--------Errors here
Set c = .FindNext(c)
y = y + 4

Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[Dave Peterson]

If SatLog in book1.xls isn't the activesheet, then this line will fail.

Workbooks("Book1.xls").Worksheets("SatLog").Range(Cells(y, 1), _
Cells(y, 2)).PasteSpecial Paste:=xlPasteAll

Those cells() reference refer to the activesheet (if the code is in a general
module) or the worksheet that owns the code (if the code is behind a worksheet).

I'd use something like this (untested):


Dim DestCell as range 'in your declaration section (at the top)

With Worksheets(1).Range("A1:A500")
Set c = .Find("Substrate # 1", LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
y = 1
Do

with workbooks("book1.xls").worksheets("satlog")
set destcell = .range(.cells(y,1),.cells(y,2))
end with

Range(c.Offset(0, 0), c.Offset(3, 1)).Copy
destcell.PasteSpecial Paste:=xlPasteAll

Set c = .FindNext(c)
y = y + 4

Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[carlos_ray86]

If SatLog in book1.xls isn't the activesheet, then this line will fail.

Workbooks("Book1.xls").Worksheets("SatLog").Range(Cells(y, 1), _
Cells(y, 2)).PasteSpecial Paste:=xlPasteAll

Those cells() reference refer to the activesheet (if the code is in a general
module) or the worksheet that owns the code (if the code is behind a worksheet).

I'd use something like this (untested):

Dim DestCell as range 'in your declaration section (at the top)

With Worksheets(1).Range("A1:A500")
Set c = .Find("Substrate # 1", LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
y = 1
Do

with workbooks("book1.xls").worksheets("satlog")
set destcell = .range(.cells(y,1),.cells(y,2))
end with

Range(c.Offset(0, 0), c.Offset(3, 1)).Copy
destcell.PasteSpecial Paste:=xlPasteAll

Set c = .FindNext(c)
y = y + 4

Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave Thank you it did work. SatLog was not the active sheet but the
change you made to the code made it work out. Thanks again.