Copy rows to master sheet

[Carolyn]

Can someone please help? I am very new to coding excel and am running
into problems constantly. I am on a tight deadline and am frightened
I am not going to make it. I am trying to copy the entire row from
one sheet to a master if the value in one of the columns is greater
than zero. I have read through all the postings I can find on the
subject - which has been helpful. The code that I have come up with
is copying everything. Also, how would I get the copied cells to
start in a row other than 1? I need to put additional reference
information in the beginning of the sheet.

I have a command button at the bottom of the sheet - which is named
chemicals - that is to start the macro.

Private Sub CommandButton1_Click()
myObject = Range("chemcounts")
Set myObject = Range("chemcounts")
Range("chemcounts").Select
Selection.Copy
Dim msg As String
msg = "Your order has been sent to the order form"
MsgBox (msg)
Dim Sh1 As Worksheet, Sh2 As Worksheet
Dim rng1 As Range, Cell As Range
Dim DestRow As Long

Set Sh1 = Worksheets("Chemicals")
Set Sh2 = Worksheets("Master")
DestRow = 1
Set rng1 = Sh1.Range("A1").CurrentRegion
Set rng1 = Intersect(rng1, Sh1.Columns(5))
' move range to start in row 2
Set rng1 = rng1.Offset(1, 0). _
Resize(rng1.Rows.Count - 1)
For Each Cell In rng1
If UCase(Cell.Value) > 0 Then
Cell.EntireRow.Copy _
Destination:=Sh2.Cells(DestRow, 1)
DestRow = DestRow + 1
End If
Next Cell

Thank you in advance for any help.

Carolyn

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Dim msg As String
Dim rng1 As Range, Cell As Range
Dim DestRow As Long

Set Sh1 = Worksheets("Chemicals")
Set Sh2 = Worksheets("Master")
DestRow = 1
Set rng1 = Sh1.Range("A1").CurrentRegion
Set rng1 = Intersect(rng1, Sh1.Columns(5)).Cells
' move range to start in row 2
Set rng1 = rng1.Offset(1, 0). _
Resize(rng1.Rows.Count - 1)
For Each Cell In rng1
If Cell.Value > 0 Then
Cell.EntireRow.Copy _
Destination:=Sh2.Cells(DestRow, 1)
DestRow = DestRow + 1
Cell.Value = 0
End If
Next Cell
End Sub

 

[chris]

lets start from the begining.
What exactly do you mean: " I am trying to copy the entire row fro
one sheet to a master if the value in one of the columns is greater than zero.
Are you asking for every cell that is > 0 to have the row copied, and if 2 seperate columns both have a Cell>0 in the same row to have it copied twice

And :"Also, how would I get the copied cells to start in a row other than 1
You can paste to any range on a sheet. Are you asking if you can find the first empty column range

Is the chemcount range just a Column: if not what is it



----- Carolyn wrote: ----

Can someone please help? I am very new to coding excel and am runnin
into problems constantly. I am on a tight deadline and am frightene
I am not going to make it. I am trying to copy the entire row fro
one sheet to a master if the value in one of the columns is greate
than zero. I have read through all the postings I can find on th
subject - which has been helpful. The code that I have come up wit
is copying everything. Also, how would I get the copied cells t
start in a row other than 1? I need to put additional referenc
information in the beginning of the sheet

I have a command button at the bottom of the sheet - which is name
chemicals - that is to start the macro

Private Sub CommandButton1_Click(
myObject = Range("chemcounts"
Set myObject = Range("chemcounts"
Range("chemcounts").Selec
Selection.Cop
Dim msg As Strin
msg = "Your order has been sent to the order form
MsgBox (msg
Dim Sh1 As Worksheet, Sh2 As Workshee
Dim rng1 As Range, Cell As Rang
Dim DestRow As Lon

Set Sh1 = Worksheets("Chemicals"
Set Sh2 = Worksheets("Master"
DestRow =
Set rng1 = Sh1.Range("A1").CurrentRegio
Set rng1 = Intersect(rng1, Sh1.Columns(5)
' move range to start in row
Set rng1 = rng1.Offset(1, 0).
Resize(rng1.Rows.Count - 1
For Each Cell In rng
If UCase(Cell.Value) > 0 The
Cell.EntireRow.Copy
Destination:=Sh2.Cells(DestRow, 1
DestRow = DestRow +
End I
Next Cel

Thank you in advance for any help

Caroly

 

[Tom Ogilvy]

I previously showed you how to get the next available row in master

set rng2 = Worksheets("Master").Cells(rows.count,1).end(xlup)(2)
cell.EntireRow.copy Destination:=rng2

or, if you want to use the Sh2 reference

set rng2 = Sh2.Cells(rows.count,1).end(xlup)(2)
cell.EntireRow.copy Destination:=rng2