Copy rows to master sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carolyn - 22 May 2004 20:09 GMT

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

Reply to this Message

Tom Ogilvy - 22 May 2004 21:37 GMT

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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 25 lines]
>
> Carolyn

Reply to this Message

Carolyn - 23 May 2004 14:47 GMT

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

> 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.
[quoted text clipped - 47 lines]
> >
> > Carolyn

Reply to this Message

Tom Ogilvy - 23 May 2004 15:14 GMT

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

Signature

Regards,
Tom Ogilvy

> Tom,
>
[quoted text clipped - 57 lines]
> > >
> > > Carolyn

Reply to this Message

David McRitchie - 23 May 2004 19:19 GMT

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" <carolynaevans@cox.net> wrote in message...
> > I have spent more hours than I care to admit trying to figure out the
[quoted text clipped - 18 lines]
> > named "Cleaning Supplies".
> > ---Carolyn

Reply to this Message

Peter Atherton - 29 May 2004 00:10 GMT

Carolyn

Copy this code into a VB Module. Change the range to suit;
it assumes that you have a sheet called Master.

Sub CopyToMaster()
Dim NextRow As Long
Dim rng As Range, item As Range
Dim wks As Worksheet
Set wks = ActiveSheet
Application.ScreenUpdating = False
With wks
Set rng = Range("b9:f9")
Set item = Range("b9:e9")
End With
Sheets("Master").Select
NextRow = Application.WorksheetFunction.CountA(Range
("A:A")) + 1
rng.Copy Sheets("Master").Cells(NextRow, 1)
item.ClearContents
wks.Select
Application.ScreenUpdating = True
End Sub

Assign your buttons to this macro.

peter_atherton@hotmail.com

Regards
Peter

>-----Original Message-----
>I have spent more hours than I care to admit trying to figure out the
[quoted text clipped - 27 lines]
>Carolyn
>.

Reply to this Message