Excel 2007 : Copying row(s) of data from one sheet to rows in another worksheet

Hi,

I'm hoping someone can help. I have created a workbook with two sheets. A sheet named "Invoice" and another named "Sales Summary Sheet" (SSS).

After clicking on a button (on the Invoice sheet), data in specific cells get copied to specific cells in SSS.

I want to copy row(s) depending on how many rows are entered into the Invoice template, to the SSS sheet. Currently rows A19:H19, A20:H20, A21:H21...to A32:H32 are rows that can have potential data in it that need to be copied to rows in the SSS sheet.

For every row in the Invoice sheet, I need to copy the data to a new row in the SSS sheet (and not overwrite existing rows of data in the SSS sheet). So for example row A19:A32 need to be copied to A2:O2, and if data in B19:B32 exists , then it also needs to be copied to A3:O3.

Once SSS is saved, the next time I open Invoice and re-enter data into A19:A32, it will add the data into SSS in cells A4:O4.

Right now I have this current code:

Private Sub CopyToSheet_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("A19:H32").End(xlUp)

'Range("A19:H32").Copy Destination:=Sheets("Sheet1").
'Adds the data to Sales Summary Sheet
'Sheet1 is the (Name) of the sheet, i.e. the object
'NOT the name in the tab of the sheet
LastRow.Offset(1, 0).Value = Sheet4.Range("H8").Value
LastRow.Offset(1, 1).Value = Sheet4.Range("H7").Value
LastRow.Offset(1, 2).Value = Sheet4.Range("B19:B32").Value
LastRow.Offset(1, 3).Value = Sheet4.Range("A19:A32").Value
LastRow.Offset(1, 4).Value = Sheet4.Range("A6").Value
LastRow.Offset(1, 5).Value = Sheet4.Range("F19:F32").Value
LastRow.Offset(1, 6).Value = Sheet4.Range("H19:H32").Value
LastRow.Offset(1, 7).Value = Sheet4.Range("H12").Value
End Sub

When I run the above code, it only copies row A19:H19 in the Invoice sheet to row A2:O2 in SSS, and DOES NOT copy the second (third, fourth...) row of data to SSS.

Any help would be GREATLY APPRECIATED!

Thanks.

Hi Dazed... Welcome to the forum.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to EDIT that post above and put code tags around that code you used. (Like shown in my signature)

Also, there is a Programming Forum for programming questions.

I'll take a look at your query for you momentarily, in the meantime fix that post above so it doesn't get tagged by the moderators. Thanks.

Your sample code also does not really shed light to enhance the verbal part of your question.

This forum allows attachments (Use the GO ADVANCED option, then the paperclip icon). Please upload a sample workbook showing an invoice with mutliple rows to transfer and then manually mockup the exact results required so we can see the BEFORE/AFTER, so to speak.

Based on the verbal question only, this is all that's really needed, which I'm sure is not correct since you showed so much other activity in your code.

Private Sub CopyToSheet_Click()
Dim NR As Long
Dim wsSSS As Worksheet
Dim wsInv As Worksheet

Set SSS = Sheets("Sales Summary Sheet")
Set wsInv = Sheets("Invoice")

NR = SSS.Range("A" & SSS.Rows.Count).End(xlUp).Row + 1
wsInv.Range("A19:H32").Copy SSS.Range("A" & NR)

End Sub

Crossposted at: http://www.mrexcel.com/forum/showthread.php?t=569450

If you'd like to post the actual workbook, I can test this out and find what is causing the problem quite readily.

Based on the example workbook you were supposed to post here but posted in your duplicate thread, then:

Private Sub CopyToSheet_Click()
Dim NR As Long
Dim Rws As Long
Dim wsSSS As Worksheet
Dim wsInv As Worksheet
Dim InvRng As Range

Set wsSSS = Sheets("Sales Summary Sheet")
Set wsInv = Sheets("Invoice")
Set InvRng = wsInv.Range("A19:A32").SpecialCells(xlConstants, xlNumbers)
Rws = InvRng.Cells.Count

With wsSSS
    NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & NR).Resize(Rws).Value = wsInv.Range("H8").Value
    .Range("B" & NR).Resize(Rws).Value = wsInv.Range("H7").Value
    .Range("C" & NR).Resize(Rws).Value = InvRng.Offset(, 1).Value
    .Range("D" & NR).Resize(Rws).Value = InvRng.Value
    .Range("G" & NR).Resize(Rws).Value = InvRng.Offset(, 5).Value
    .Range("H" & NR).Resize(Rws).Value = InvRng.Offset(, 7).Value
End With

End Sub

Also, in your Module1, these macros simplify down to:

Sub Button27_Click()
    Range("A19:B32, F19:F32").Clear
End Sub

Sub Clear()
    Range("A19:F32").ClearContents
End Sub

Sub ClearSave()
    Call Clear     'you should rename this macro, "clear" is a VBA function, too.
    ActiveWorkbook.Save
End Sub

Sub Print2()
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=2
End Sub

it works! You rock. Thank you!