Find, Copy offset to offset on other sheet, Run-time 1004.
 

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
..Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

Find, Copy offset to offset on other sheet, Run-time 1004.
 

Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop




Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

I tried it from a general module in the sales sheet. Still err's on
selecting from forecast sheet.
I tried it from a module in PersonalWorkbook. Same error, same Select.
:(

Finny wrote:
That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop




Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

.....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

It seems the difference is that you assigned the copyarray and
pastecell to range variables and copy/pasted the range vars.
While I was trying to copy directly from the array to the other sheet.

You said:
".Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
says to look at cells(rngfcitem.row+1,26) and grab the value that's in
that cell. Then use that as an address (or Name) in the .Range()
portion.
I bet you really just want to use that cell, but I've been wrong lots
of times."

I assumed that line give the destination for the paste and paste. Go
figure.
So I think you bet right, I that was the left most cell I wanted to
paste to.

"use that as an address in the .Range() portion" for what?

Thanks again,
Finny

Finny wrote:
Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

I think it makes the code much easier to understand (and update later!) when you
drop the .selects. And using variables to represent what you need makes it
easier to drop those .selects.

Finny wrote:

It seems the difference is that you assigned the copyarray and
pastecell to range variables and copy/pasted the range vars.
While I was trying to copy directly from the array to the other sheet.

You said:
".Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
says to look at cells(rngfcitem.row+1,26) and grab the value that's in
that cell. Then use that as an address (or Name) in the .Range()
portion.
I bet you really just want to use that cell, but I've been wrong lots
of times."

I assumed that line give the destination for the paste and paste. Go
figure.
So I think you bet right, I that was the left most cell I wanted to
paste to.

"use that as an address in the .Range() portion" for what?

Thanks again,
Finny

Finny wrote:
Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

alright then, thanks again.

I only posted the first thank you once. but google was tripping

cheers!

Finny wrote:
Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Find, Copy offset to offset on other sheet, Run-time 1004.
 

You're welcome. And I forgive google <vbg.

Finny wrote:

alright then, thanks again.

I only posted the first thank you once. but google was tripping

cheers!

Finny wrote:
Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson