I have a 2 tab spreadsheet. On sheet 1 (SBX WORK SHEET) I calculate unit
price (E18) by multiplying the actual cost (G18) by a markup in cell (E14).
By changing the markup value in E14 the salemen can adjust their margin. That
works fine.

Tab 2 (SBX COST SHEET) runs a macro that works on other spreadsheets where
if the quantity is a number the entire line is written to a "quote sheet".
The macro is below:
Option Explicit
Sub mastertest2()
Dim rSource As Range
Dim rCell As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("SBX WORK SHEET"))
With wks
FirstRow = 4
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

For iRow = LastRow To FirstRow Step -1
Set rCell = wks.Cells(iRow, 4)
With rCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Worksheets("SBX COST SHEET").Rows(17).Insert
..EntireRow.Copy _
Destination:=Worksheets("SBX COST SHEET").Range("a17")
End If
End If
End With
Next iRow
Next wks
End Sub
The problem is when I run the macro I get a #REF error in the Unit Price
Column on the SBX COST SHEET. It appears that it does not like the value. Any
help will be appreciated.

Jerry

I'm guessing that you have formulas in the row you are copying and you should
probably be doing a PasteSpecial Paste:=xlPasteValues.

"Jerry Foley" wrote:

I have a 2 tab spreadsheet. On sheet 1 (SBX WORK SHEET) I calculate unit
price (E18) by multiplying the actual cost (G18) by a markup in cell (E14).
By changing the markup value in E14 the salemen can adjust their margin. That
works fine.

Tab 2 (SBX COST SHEET) runs a macro that works on other spreadsheets where
if the quantity is a number the entire line is written to a "quote sheet".
The macro is below:
Option Explicit
Sub mastertest2()
Dim rSource As Range
Dim rCell As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("SBX WORK SHEET"))
With wks
FirstRow = 4
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

For iRow = LastRow To FirstRow Step -1
Set rCell = wks.Cells(iRow, 4)
With rCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Worksheets("SBX COST SHEET").Rows(17).Insert
.EntireRow.Copy _
Destination:=Worksheets("SBX COST SHEET").Range("a17")
End If
End If
End With
Next iRow
Next wks
End Sub
The problem is when I run the macro I get a #REF error in the Unit Price
Column on the SBX COST SHEET. It appears that it does not like the value. Any
help will be appreciated.

Jerry

Thanks,
Where would that go in the macro?

"JLGWhiz" wrote:

I'm guessing that you have formulas in the row you are copying and you should
probably be doing a PasteSpecial Paste:=xlPasteValues.

"Jerry Foley" wrote:

I have a 2 tab spreadsheet. On sheet 1 (SBX WORK SHEET) I calculate unit
price (E18) by multiplying the actual cost (G18) by a markup in cell (E14).
By changing the markup value in E14 the salemen can adjust their margin. That
works fine.

Tab 2 (SBX COST SHEET) runs a macro that works on other spreadsheets where
if the quantity is a number the entire line is written to a "quote sheet".
The macro is below:
Option Explicit
Sub mastertest2()
Dim rSource As Range
Dim rCell As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("SBX WORK SHEET"))
With wks
FirstRow = 4
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

For iRow = LastRow To FirstRow Step -1
Set rCell = wks.Cells(iRow, 4)
With rCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Worksheets("SBX COST SHEET").Rows(17).Insert
.EntireRow.Copy _
Destination:=Worksheets("SBX COST SHEET").Range("a17")
End If
End If
End With
Next iRow
Next wks
End Sub
The problem is when I run the macro I get a #REF error in the Unit Price
Column on the SBX COST SHEET. It appears that it does not like the value. Any
help will be appreciated.

Jerry

Replace these lines:

..EntireRow.Copy _
Destination:=Worksheets("SBX COST SHEET").Range("a17")

With:

..EntireRow.Copy
Worksheets("SBX COST SHEET").Range("a17") _
.PasteSpecial Paste:=xlPasteValues





"Jerry Foley" wrote:

Thanks,
Where would that go in the macro?

"JLGWhiz" wrote:

I'm guessing that you have formulas in the row you are copying and you should
probably be doing a PasteSpecial Paste:=xlPasteValues.

"Jerry Foley" wrote:

I have a 2 tab spreadsheet. On sheet 1 (SBX WORK SHEET) I calculate unit
price (E18) by multiplying the actual cost (G18) by a markup in cell (E14).
By changing the markup value in E14 the salemen can adjust their margin. That
works fine.

Tab 2 (SBX COST SHEET) runs a macro that works on other spreadsheets where
if the quantity is a number the entire line is written to a "quote sheet".
The macro is below:
Option Explicit
Sub mastertest2()
Dim rSource As Range
Dim rCell As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("SBX WORK SHEET"))
With wks
FirstRow = 4
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

For iRow = LastRow To FirstRow Step -1
Set rCell = wks.Cells(iRow, 4)
With rCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Worksheets("SBX COST SHEET").Rows(17).Insert
.EntireRow.Copy _
Destination:=Worksheets("SBX COST SHEET").Range("a17")
End If
End If
End With
Next iRow
Next wks
End Sub
The problem is when I run the macro I get a #REF error in the Unit Price
Column on the SBX COST SHEET. It appears that it does not like the value. Any
help will be appreciated.

Jerry