Help?!?! Pulling Values, Auto-Populating & Printing

[KRich1387]

Hello everyone!

Let me first say that I am a complete newb when it comes to Excel Macros. I am pretty interemediate in terms of Excel in general, but can't quite figure out macros.

I am working on a report where I have a sheet that auto-populates various values (i.e. vehicle's license plate, color, drive name, etc.) based on the VIN#. I setup a drop down menu that is linked to a seperate sheet with every (400+) unique VIN# - when one is selected, the values update. It works great.

My issue: I need to develop a macro that takes each individual/unique VIN# (i.e. A2:A459) from one sheet and paste it into the specific cell (A2) of my other sheet that auto-populates. I then need to print that sheet, and repeat for each VIN#.

I tried recording a macro but after reading somewhat similar posts I know there is a simpler way to write one without having to type the same lines 459 times. Here is what I have:

Sub Macro1()
'
' Macro1 Macro
'
'Application.ScreenUpdating = True
Sheets("Fleet Working Spreadsheet").Select
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("template").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("template").PrintOut
Sheets("Fleet Working Spreadsheet").Select
Range("A3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("template").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("template").PrintOut
Sheets("Fleet Working Spreadsheet").Select
Range("A4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("template").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("template").PrintOut

End Sub

etc.

Any help would be greatly appreciated!

 

[Kamolga]

Hi,

Welcome to the forum

Sub Print()
Dim i as long
i=2  [COLOR="#008000"]'2 is starting line[/COLOR]
Do until i=460  [COLOR="#008000"]'460 is last line+1 so stops line 459 -> can also make it dynamic (=until last row) if required[/COLOR]
Sheets("Fleet Working Spreadsheet").Select
Cells(i,1).Copy
Sheets("template").Select
 Range("A2").Select
 ActiveSheet.Paste
 Sheets("template").PrintOut
i=i+1
Loop
End sub

 

[KRich1387]

Thanks so much for the quick reply Kamolga. Worked great!

Now of course the boss man wants something a little different but thanks again for your help.