Copy rows to master sheet
 

I have spent more hours than I care to admit trying to figure out the
code for copying rows that have updated date to a master sheet. I
have a command button on the bottom of four sheets that I want the
user to press and send information that has changed to the master
sheet. I want them to be in sequential order (no spaces).

Upon sending it, I want the changes to the cells to revert to the
original state. They are supply order forms. The only thing that
changes is the number of units that someone wants and the total price
for the units.

For example:

Sku# Item Name #Wanted Unit Price Total Cost
blabla towels 4 $2.00 $8.00

After they send the information to the master sheet, I want the number
wanted and total cost to revert to zero.

The sheets are named according to the product...The first sheet is
named "Cleaning Supplies".

Can someone please help me? I feel like I have read through every
posting and can't figure it out. I usually do not use excel, but the
client that I am working for prefers it.

Thank you in advance for any help.

Carolyn

Copy rows to master sheet
 

just loop through the #wanted column and copy any rows that are not zero.
Place the row in the next available row on the master sheet.

then set the #wanted to zero.

I assume total cost is calculated with a formula, so setting #wanted to zero
would make total cost zero.

to find the next available row on the master sheet

Dim rng as Range, rng1 as Range
With Worksheets("Cleaning Supplies)
set rng = .Range(.cells(2,1),.Cells(rows.count,1).end(xlup))
End With
for each cell in rng
if cell.offset(0,2) < 0 then
set rng1 = Worksheets("Master").Cells(rows.count,1).end(xlup) (2)
cell.EntireRow.copy Destination:=rng1
cell.offset(0,2).Value = 0
end if
Next

--
Regards,
Tom Ogilvy


"Carolyn" wrote in message
om...
I have spent more hours than I care to admit trying to figure out the
code for copying rows that have updated date to a master sheet. I
have a command button on the bottom of four sheets that I want the
user to press and send information that has changed to the master
sheet. I want them to be in sequential order (no spaces).

Upon sending it, I want the changes to the cells to revert to the
original state. They are supply order forms. The only thing that
changes is the number of units that someone wants and the total price
for the units.

For example:

Sku# Item Name #Wanted Unit Price Total Cost
blabla towels 4 $2.00 $8.00

After they send the information to the master sheet, I want the number
wanted and total cost to revert to zero.

The sheets are named according to the product...The first sheet is
named "Cleaning Supplies".

Can someone please help me? I feel like I have read through every
posting and can't figure it out. I usually do not use excel, but the
client that I am working for prefers it.

Thank you in advance for any help.

Carolyn

Copy rows to master sheet
 

Tom,

Thank you for your advice. I am still a little clueless though. I
don't know how to set up the loop. I have never written macros in
excel before. I am pretty lost. Any additional help you could
provide would be great.

Thanks again,
Carolyn

"Tom Ogilvy" wrote in message ...
just loop through the #wanted column and copy any rows that are not zero.
Place the row in the next available row on the master sheet.

then set the #wanted to zero.

I assume total cost is calculated with a formula, so setting #wanted to zero
would make total cost zero.

to find the next available row on the master sheet

Dim rng as Range, rng1 as Range
With Worksheets("Cleaning Supplies)
set rng = .Range(.cells(2,1),.Cells(rows.count,1).end(xlup))
End With
for each cell in rng
if cell.offset(0,2) < 0 then
set rng1 = Worksheets("Master").Cells(rows.count,1).end(xlup) (2)
cell.EntireRow.copy Destination:=rng1
cell.offset(0,2).Value = 0
end if
Next

--
Regards,
Tom Ogilvy


"Carolyn" wrote in message
om...
I have spent more hours than I care to admit trying to figure out the
code for copying rows that have updated date to a master sheet. I
have a command button on the bottom of four sheets that I want the
user to press and send information that has changed to the master
sheet. I want them to be in sequential order (no spaces).

Upon sending it, I want the changes to the cells to revert to the
original state. They are supply order forms. The only thing that
changes is the number of units that someone wants and the total price
for the units.

For example:

Sku# Item Name #Wanted Unit Price Total Cost
blabla towels 4 $2.00 $8.00

After they send the information to the master sheet, I want the number
wanted and total cost to revert to zero.

The sheets are named according to the product...The first sheet is
named "Cleaning Supplies".

Can someone please help me? I feel like I have read through every
posting and can't figure it out. I usually do not use excel, but the
client that I am working for prefers it.

Thank you in advance for any help.

Carolyn

Copy rows to master sheet
 

I gave you the code that does it.

Sub Copyrows()
Dim rng as Range, rng1 as Range
With Worksheets("Cleaning Supplies)
set rng = .Range(.cells(2,1),.Cells(rows.count,1).end(xlup))
End With
for each cell in rng
if cell.offset(0,2) < 0 then
set rng1 = Worksheets("Master").Cells(rows.count,1).end(xlup) (2)
cell.EntireRow.copy Destination:=rng1
cell.offset(0,2).Value = 0
end if
Next
End Sub

Now I have added the declaration for the code.

Paste it into a general module in your workbook. Change the names of the
sheets to match your situation.

then do Tools=Macro=Macros
highlight Copyrows and click run

--
Regards,
Tom Ogilvy





"Carolyn" wrote in message
om...
Tom,

Thank you for your advice. I am still a little clueless though. I
don't know how to set up the loop. I have never written macros in
excel before. I am pretty lost. Any additional help you could
provide would be great.

Thanks again,
Carolyn

"Tom Ogilvy" wrote in message
...
just loop through the #wanted column and copy any rows that are not
zero.
Place the row in the next available row on the master sheet.

then set the #wanted to zero.

I assume total cost is calculated with a formula, so setting #wanted to
zero
would make total cost zero.

to find the next available row on the master sheet

Dim rng as Range, rng1 as Range
With Worksheets("Cleaning Supplies)
set rng = .Range(.cells(2,1),.Cells(rows.count,1).end(xlup))
End With
for each cell in rng
if cell.offset(0,2) < 0 then
set rng1 = Worksheets("Master").Cells(rows.count,1).end(xlup) (2)
cell.EntireRow.copy Destination:=rng1
cell.offset(0,2).Value = 0
end if
Next

--
Regards,
Tom Ogilvy


"Carolyn" wrote in message
om...
I have spent more hours than I care to admit trying to figure out the
code for copying rows that have updated date to a master sheet. I
have a command button on the bottom of four sheets that I want the
user to press and send information that has changed to the master
sheet. I want them to be in sequential order (no spaces).

Upon sending it, I want the changes to the cells to revert to the
original state. They are supply order forms. The only thing that
changes is the number of units that someone wants and the total price
for the units.

For example:

Sku# Item Name #Wanted Unit Price Total Cost
blabla towels 4 $2.00 $8.00

After they send the information to the master sheet, I want the number
wanted and total cost to revert to zero.

The sheets are named according to the product...The first sheet is
named "Cleaning Supplies".

Can someone please help me? I feel like I have read through every
posting and can't figure it out. I usually do not use excel, but the
client that I am working for prefers it.

Thank you in advance for any help.

Carolyn

Copy rows to master sheet
 

Hi Carolyn,

The code explicitly names both sheets (master and
one of your other sheets), you can actually run the macro
while you are on any worksheet.

There is a typo in the macro as it is missing the ending
double quote after "cleaning supplies" which should
show up as RED indicating a syntax error when you
paste the code into a module. One other thing with
Options Explicit all variables including the variable
"cells" should be declared (dimensioned) for that
you would have received an error indicating an
undeclared variable.

You can use a macro without understanding exactly
how it works. Test on a copy of your workbook.

To retest make sure there are item counts on your
Cleaning Supplies worksheet.

So Tom's code is

Option Explicit
Sub Copyrows()
Dim rng As Range, rng1 As Range, cell As Range
With Worksheets("Cleaning Supplies")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
If cell.Offset(0, 2) < 0 Then
Set rng1 = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp)(2)
cell.EntireRow.Copy Destination:=rng1
cell.Offset(0, 2).Value = 0
End If
Next
End Sub

Tom gave you instructions to intall and run the code, but
if that doesn't work out for you. You could look over my
page Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

[please refer to the thread for 2 intermediate replies]

"Carolyn" wrote in message...
I have spent more hours than I care to admit trying to figure out the
code for copying rows that have updated date to a master sheet. I
have a command button on the bottom of four sheets that I want the
user to press and send information that has changed to the master
sheet. I want them to be in sequential order (no spaces).

Upon sending it, I want the changes to the cells to revert to the
original state. They are supply order forms. The only thing that
changes is the number of units that someone wants and the total price
for the units.

For example:
Sku# Item Name #Wanted Unit Price Total Cost
blabla towels 4 $2.00 $8.00

After they send the information to the master sheet, I want the number
wanted and total cost to revert to zero.

The sheets are named according to the product...The first sheet is
named "Cleaning Supplies".
---Carolyn