Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows to master sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows to master sheet
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 -- Regards, Tom Ogilvy "Carolyn" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows to master sheet
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 -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Carolyn" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows to master sheet
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 Cell0 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from master sheet new | New Users to Excel | |||
copy data from master sheet | New Users to Excel | |||
copy data from master sheet | Excel Discussion (Misc queries) | |||
copy data from master sheet | Excel Discussion (Misc queries) | |||
Copy rows to master sheet | Excel Programming |