If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|