Macro help please - automatic copy, paste, print, next

[enigmam]

Hello

I'm in need of some macro help, and I was hoping someone here might be able to help.

I have a spreadsheet with two main tabs. One is full of data, mainly a unique reference number and columns of data related to that unique reference number. The main tab, is a form with a place to input a unique reference number. Once the unique number is entered the form is filled automatically.

There are around 2,000 unique numbers in column D in the "data" tab. I need a macro to copy the unique reference number in column D, row 3, paste it into the "main" tab say H2, then let the spreadsheet update, then print the main tab. Then copy the unique reference number in column D, row 4, paste it into the "main" tab H2, then let the spreadsheet update, then print........ all the way until the last unique reference number has been done around column D row 2000.

The one strange thing I know I am going to have trouble with is the unique number starts with several zeros, some with less than others. When I copy and paste the cell, sometimes it removes the zeros.


I really have no idea where to start and any help from anyone will be greatly appreciated.

 

[dave3009]

Hi and Welcome :D

Assuming the form will update auotmatically on entry into Cell H2 and you actually want to print 2000 sheets then

Sub print2000()
Dim lst As Long
    lst = Sheets("Data").Range("D" & Rows.Count).End(xlUp).Row
    
    For i = 3 To lst
        With Sheets("Main")
            .Range("H2") = Sheets("Data").Range("D" & i)
            .PrintOut
        End With
    Next i
    
End Sub

 

[enigmam]

That's brilliant, thanks for your help.

I'm hoping to learn macro/vba, so I'll have a good luck through the above to see if I can learn from it.

One quick more quick question though if that's OK. I can kind of understand most of what the macro/script (?) does, I can see what to change if I suddenly need to amend the input cell for example. I take it if I work out I need to print, for example 1876 times, it will print just one copy each and stop when it runs out of data. Also the print2000, is that just the name of the macro?

 

[dave3009]

No, the macro does that by assessing lst which is the last used cell in column D of data sheet. You do not need to figure this out for yourself, Happy days

 

[dave3009]

I'll breakdown each step

Sub print2000() 'This line is just the name of the macro, nothing else
Dim lst As Long 'You can use Integer here as you only have 2000 rows
    lst = Sheets("Data").Range("D" & Rows.Count).End(xlUp).Row 'Find last row in Data sheet
    
    For i = 3 To lst 'loop from 3 to the last row
        With Sheets("Main")
            .Range("H2") = Sheets("Data").Range("D" & i) ' Range H2 can be changed to suit your needs as can the column D reference
            .PrintOut 'The sheet has been updated so print
        End With
    Next i ' Next row please
    
End Sub

 

[enigmam]

That's fantastic.

Thanks very much for all your help and for going to the trouble of breaking it down step by step for me.

Much appreciated