| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Problem
Sorry to bother you guys again.
But I'm stuck, and you where all such a great help last time. I have an invoice sheet and a Product Summary sheet. I want to take the rows from my Invoice sheet and add them to my summary sheet. My invoice sheet has 27 rows, but only some of the rows will actually have items in them. When I click on a cmd button I want to take the rows that have items in them and add the information to the bottom of my summary list. I've tried macros, and using a dynamic name range.. but I can't get it to work :( Can anyone help me with this? Thanks Wally |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Problem
try this code. I assumed that column A on Summary sheet has data in each
row. needed to find the last row of the summary sheet and made an assuption that Column A has data in it for every row. Sub AddToSummary() Const InvoiceSh = "Invoice" Const SummarySh = "Summary" Sheets(InvoiceSh).Activate InvLastrow = Sheets(InvoiceSh).Cells(Rows.Count, 1).End(xlUp).Row Sheets(SummarySh).Activate SumLastRow = Sheets(SummarySh).Cells(Rows.Count, 1).End(xlUp) 'move to 1st blank line SumLastRow = SumLastRow + 1 For InvRowCount = 1 To InvLastrow 'check for empty rows Sheets(InvoiceSh).Activate InvLastcolumn = Sheets(InvoiceSh).Cells(InvRowCount, Columns.Count).End(xlToLeft).Column If (InvLastcolumn 1) Or Not IsEmpty(Sheets(InvoiceSh).Cells(InvRowCount, 1)) Then Sheets(InvoiceSh).Cells(InvRowCount, 1).EntireRow.Copy _ Destination:=Sheets(SummarySh).Cells(SumLastRow, 1) SumLastRow = SumLastRow + 1 End If Next InvRowCount End Sub "WTG" wrote: Sorry to bother you guys again. But I'm stuck, and you where all such a great help last time. I have an invoice sheet and a Product Summary sheet. I want to take the rows from my Invoice sheet and add them to my summary sheet. My invoice sheet has 27 rows, but only some of the rows will actually have items in them. When I click on a cmd button I want to take the rows that have items in them and add the information to the bottom of my summary list. I've tried macros, and using a dynamic name range.. but I can't get it to work :( Can anyone help me with this? Thanks Wally |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Problem
Thanks Joel, I tried it, but I kept coming up with debug errors. and I don't know much about what I was doing. :( I have been searching and Came up with this, Private Sub CommandButton1_Click() LastRow = Sheets("Invoice").Range("A65536").End(xlUp).Row + 1 Range("A6:H32").Select Selection.Copy Sheets("Summary").Select Range("LastRow").Select ActiveSheet.Paste End Sub But this gives me debug errors to :( On Wed, 21 Mar 2007 17:13:14 -0700, Joel wrote: try this code. I assumed that column A on Summary sheet has data in each row. needed to find the last row of the summary sheet and made an assuption that Column A has data in it for every row. Sub AddToSummary() Const InvoiceSh = "Invoice" Const SummarySh = "Summary" Sheets(InvoiceSh).Activate InvLastrow = Sheets(InvoiceSh).Cells(Rows.Count, 1).End(xlUp).Row Sheets(SummarySh).Activate SumLastRow = Sheets(SummarySh).Cells(Rows.Count, 1).End(xlUp) 'move to 1st blank line SumLastRow = SumLastRow + 1 For InvRowCount = 1 To InvLastrow 'check for empty rows Sheets(InvoiceSh).Activate InvLastcolumn = Sheets(InvoiceSh).Cells(InvRowCount, Columns.Count).End(xlToLeft).Column If (InvLastcolumn 1) Or Not IsEmpty(Sheets(InvoiceSh).Cells(InvRowCount, 1)) Then Sheets(InvoiceSh).Cells(InvRowCount, 1).EntireRow.Copy _ Destination:=Sheets(SummarySh).Cells(SumLastRow, 1) SumLastRow = SumLastRow + 1 End If Next InvRowCount End Sub "WTG" wrote: Sorry to bother you guys again. But I'm stuck, and you where all such a great help last time. I have an invoice sheet and a Product Summary sheet. I want to take the rows from my Invoice sheet and add them to my summary sheet. My invoice sheet has 27 rows, but only some of the rows will actually have items in them. When I click on a cmd button I want to take the rows that have items in them and add the information to the bottom of my summary list. I've tried macros, and using a dynamic name range.. but I can't get it to work :( Can anyone help me with this? Thanks Wally |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Problem
WTG: there appears to have been some lines that were too long and created the
errors. I add continuation character (_ underscore) to get rid of those errors. I also replaced the CommandButton1_Click() with my code try the code below Private Sub CommandButton1_Click() Const InvoiceSh = "Invoice" Const SummarySh = "Summary" Sheets(InvoiceSh).Activate InvLastrow = Sheets(InvoiceSh).Cells(Rows.Count, 1).End(xlUp).Row Sheets(SummarySh).Activate SumLastRow = Sheets(SummarySh).Cells(Rows.Count, 1).End(xlUp) 'move to 1st blank line SumLastRow = SumLastRow + 1 For InvRowCount = 1 To InvLastrow 'check for empty rows Sheets(InvoiceSh).Activate InvLastcolumn = Sheets(InvoiceSh).Cells(InvRowCount, _ Columns.Count).End(xlToLeft).Column If (InvLastcolumn 1) Or Not _ IsEmpty(Sheets(InvoiceSh).Cells(InvRowCount, 1)) Then Sheets(InvoiceSh).Cells(InvRowCount, 1).EntireRow.Copy _ Destination:=Sheets(SummarySh).Cells(SumLastRow, 1) SumLastRow = SumLastRow + 1 End If Next InvRowCount End Sub "WTG" wrote: Thanks Joel, I tried it, but I kept coming up with debug errors. and I don't know much about what I was doing. :( I have been searching and Came up with this, Private Sub CommandButton1_Click() LastRow = Sheets("Invoice").Range("A65536").End(xlUp).Row + 1 Range("A6:H32").Select Selection.Copy Sheets("Summary").Select Range("LastRow").Select ActiveSheet.Paste End Sub But this gives me debug errors to :( On Wed, 21 Mar 2007 17:13:14 -0700, Joel wrote: try this code. I assumed that column A on Summary sheet has data in each row. needed to find the last row of the summary sheet and made an assuption that Column A has data in it for every row. Sub AddToSummary() Const InvoiceSh = "Invoice" Const SummarySh = "Summary" Sheets(InvoiceSh).Activate InvLastrow = Sheets(InvoiceSh).Cells(Rows.Count, 1).End(xlUp).Row Sheets(SummarySh).Activate SumLastRow = Sheets(SummarySh).Cells(Rows.Count, 1).End(xlUp) 'move to 1st blank line SumLastRow = SumLastRow + 1 For InvRowCount = 1 To InvLastrow 'check for empty rows Sheets(InvoiceSh).Activate InvLastcolumn = Sheets(InvoiceSh).Cells(InvRowCount, Columns.Count).End(xlToLeft).Column If (InvLastcolumn 1) Or Not IsEmpty(Sheets(InvoiceSh).Cells(InvRowCount, 1)) Then Sheets(InvoiceSh).Cells(InvRowCount, 1).EntireRow.Copy _ Destination:=Sheets(SummarySh).Cells(SumLastRow, 1) SumLastRow = SumLastRow + 1 End If Next InvRowCount End Sub "WTG" wrote: Sorry to bother you guys again. But I'm stuck, and you where all such a great help last time. I have an invoice sheet and a Product Summary sheet. I want to take the rows from my Invoice sheet and add them to my summary sheet. My invoice sheet has 27 rows, but only some of the rows will actually have items in them. When I click on a cmd button I want to take the rows that have items in them and add the information to the bottom of my summary list. I've tried macros, and using a dynamic name range.. but I can't get it to work :( Can anyone help me with this? Thanks Wally |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy/Paste Problem | Excel Worksheet Functions | |||
| Copy and Paste Problem | Excel Discussion (Misc queries) | |||
| macro copy problem | Excel Discussion (Misc queries) | |||
| copy paste problem? | Excel Discussion (Misc queries) | |||
| Copy Problem | Excel Discussion (Misc queries) | |||
Linear Mode
