Can't get macro to loop correctly

[andre232]

Hello!

I have been trying for a few days to get this macro to loop. It needs to take info from page 1 and paste into specific areas on sheet 1 then print. I have it doing that much but I need it to do this for each row on page 1 until there is no more data. It is to print some very basic invoices. I have tried many variations with no luck. Any help or advice would be appreciated! Thank you.


Sub InvoiceProp()
'
' InvoiceProp Macro
'


'
Sheets("Page1_1").Select
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("D19").Select
ActiveSheet.Paste
Sheets("Page1_1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D20").Select
ActiveSheet.Paste
Sheets("Page1_1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D21").Select
ActiveSheet.Paste
Sheets("Page1_1").Select
ActiveCell.Offset(0, -4).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub

 

[mse330]

Welcome to the board andre232

We need more clarification to be able to help you out. What I could understand from your code is the following:

1. Copy the value of cell A1 in sheet Page1_1 - Always copy the same value ?!
2. Paste it in cell D19 in sheet Sheet1 - You want to loop here until which cell?
3. What do you have in cell A10 in Sheet1 ?
4. Are you printing invoices from Sheet1 only or from multiple sheets?

 

[Fluff]

Hi & welcome to the board.
Could you please explain exactly what you're after.
Your code is impossible to understand, because of the ActiveCell references. As we cannot see your sheet, we have no way of knowing which cell is active.
Cheers

 

[andre232]

Sorry about the formatting guys still new at all this. The starting cell for page 1 is C15. As simple as I can explain since I can't attach a sample: Page 1 is an overview of charges for different departments. Columns C, D, and E on page 1 represent values for various charges. These columns need to be copied and pasted into cells D19, D20, and D21 on sheet 1. Then column A on Page 1 goes to A10 on sheet1. Column A is just the department name to whom the invoice is directed. These invoices do not need to be individually saved so after this info gets pasted to sheet 1 I'd like it to print and then move down to the next row on page 1 and replace with the info from that row. It starts on row 15 and I've been trying to get a do until isempty loop to work but hasn't so far. Hopefully that makes sense!

 

[Fluff]

Give this a go.
It assumes that there is no data below, the data you want to print.

Sub InvoiceProp()

    Dim UsdRws As Long
    Dim Cnt As Long

    Sheets("Page1_1").Select
    UsdRws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Sheets("Sheet1")
        For Cnt = 15 To UsdRws
            .Range("D19") = Range("D" & Cnt)
            .Range("D20") = Range("C" & Cnt)
            .Range("D21") = Range("E" & Cnt)
            .Range("A10") = Range("A" & Cnt)
            .PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        Next Cnt
    End With

End Sub

 

[andre232]

That is exactly what I was going for! Thank you so much

 

[Fluff]

Glad to help & thanks for the feedback