Help with a macro

[muhleebbin]

Thank you in advance with your assistance on this!

I have the following partially functioning macro, however it prints off unnecessary pages beyond the data filled in column T. Is there a way to designate the "r" to finish at the same point as the "Lastrow"? Also is there a 'cleaner' way to code this?

Dim Lastrow As Integer
Lastrow = Sheet12.Range("AA4")

Sheet12.Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T" & Lastrow)
Sheet12.Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U" & Lastrow)
Sheet12.Range("Y2").Select
Selection.AutoFill Destination:=Range("Y2:Y" & Lastrow)

Dim r As Long

For r = 2 To Sheet12.Cells(Sheet11.Rows.Count, "T").End(xlUp).Row Step 2
Sheet12.Range("D13:H14").Value = Sheet12.Cells(r, "T").Value
Sheet12.Range("D38:H39").Value = Sheet12.Cells(r + 1, "T").Value
Sheet12.PrintOut
Next r

End Sub

 

[mrshl9898]

How's this?

Dim sht As Worksheet
Dim Lastrow As Integer


Set sht = Sheet12


Lastrow = sht.Cells(sht.Rows.Count, "AA").End(xlUp).Row


sht.Range("T2:U2").AutoFill Destination:=Range("T3:U" & Lastrow)
sht.Range("Y2").AutoFill Destination:=Range("Y3:Y" & Lastrow)


Dim r As Long


For r = 2 To Sheet12.Cells(Sheet11.Rows.Count, "T").End(xlUp).Row Step 2
Sheet12.Range("D13:H14").Value = Sheet12.Cells(r, "T").Value
Sheet12.Range("D38:H39").Value = Sheet12.Cells(r + 1, "T").Value
Sheet12.PrintOut
Next r

 

[Michael M]

mine

Sub MM1()
Dim sht As Worksheet, Lastrow As Long, r As Long
Set sht = Sheet12
Lastrow = sht.Cells(sht.Rows.Count, "AA").End(xlUp).Row
sht.Range("T3:U" & Lastrow) = Range("T2:U2")
sht.Range("Y3:Y" & Lastrow) = Range("Y2")
    For r = 2 To sht.Cells(Sheet11.Rows.Count, "T").End(xlUp).Row Step 2
        sht.Range("D13:H14").Value = sht.Cells(r, "T").Value
        sht.Range("D38:H39").Value = sht.Cells(r + 1, "T").Value
        sht.PrintOut
    Next r
End Sub

 

[muhleebbin]

Thanks Michael & mrshl! Both codes worked however they still printed an unnecessary amount of pages after the last row of data. I've tried with two example data sets (1st having 3 entries and a second having 16 entries) and each time they printed more than the 2 pages and 8 pages necessary. Cell AA4 contains the number of rows that the data is maxed out at and where "r" should stop printing

 

[Michael M]

Ok, if you press CTRL + END, where does the cursor go ??
If it goes waaay down the page somewhere, Highlight the first row past the data then press CTRL + SHIFT+ down Arrow.....Rclick any row header and select DELETE.
Save >> close>>and reopen.
See if the print job is now smaller

 

[muhleebbin]

That worked! Thanks Michael

 

[muhleebbin]

I do have to add a clear contents line it seems. Whenever the data set shrinks down, column T still has "" from the previous fill and prints unnecessary pages.

This should work to fulfill that correct? Sorry, stepped away for the day and would test it out myself but just thought i'd ask

sht.Range("T3:T").ClearContents

 

[Michael M]

change this

sht.Range("T3:T").ClearContents

to

sht.Range("T3:T" & Lastrow).ClearContents