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
(E-Mail Removed) wrote:
>
> 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
Similar Threads
