Fill data from list to exact cell, print, repeat

BlackThorne

New Member
Joined
Nov 16, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,
please I need help with a little work project in Excel.
We have parking cards for our employees (300+) and they are valid only till the end of the year, which means we will have to print out new ones. I have uploaded image of what our cards look like in Excel, two fit on one page (A4).

My problem is I have 300+ licence plate numbers downloaded from our internal database as another excel sheet (SPZ on the image) and I basically need all of them filled one by one to the cards.

Solution I came up with, but am unable to write a macro for is to:
1) Copy first two licence plate numbers from top down list and insert them to the cells I need them in.
2) Print the page (two parking cards)
3a) Delete first two rows and repeat from 1) till blank (end of list)
3b) Copy next two licence plate numbers below and repeat from 2) till blank (end of list)
- I did and tested first two points as macro and it works, but with my limited knowledge of VBA I am unable to write a loop for it or make it stop when comes to blank.

Can you please help me? Or can you think of any other solution for these 300+ licence plate numbers to be printed out without need of manually copying them to cell and print and repeat?

Thank you very much for your help.
 

Attachments

  • Licence plate number.JPG
    Licence plate number.JPG
    75.1 KB · Views: 8

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
437
Office Version
  1. 365
Platform
  1. Windows
You could use a FOR NEXT loop to advance through your rows. It would be something along the lines of:
Set sh = ActiveSheet
RowCount = 300
For Each rw In sh.Rows
'insert your code to populate the first two lines into the form'
'insert your code to print the form'
RowCount = RowCount + 2
Next rw
 

BlackThorne

New Member
Joined
Nov 16, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi Candyman8019,

Thank you very much for your answer. My code does look like this (see below) when I insert mine into your loop.
I am not sure why (maybe because of my poorly specified code), but it keeps looping only the first two cells I started with and never goes to another. I solved that by adding line which deletes first two rows which makes the loop to add and print the data from next two rows, BUT the second problem is that it never stops. I changed RowCount to equal 4 or just 2, but the loop keeps on and on (till I cancel the print, because I am "printing" to PDF for testing purposes).
Can you please take a second look at this? :)

Set sh = ActiveSheet
RowCount = 300
For Each rw In sh.Rows
Range("U54").Select
Application.CutCopyMode = False
Selection.Copy
Range("G21:J22").Select
ActiveSheet.Paste
Range("U55").Select
Application.CutCopyMode = False
Selection.Copy
Range("G47:J48").Select
ActiveSheet.Paste
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
RowCount = RowCount + 2
Next rw
 

Candyman8019

Active Member
Joined
Dec 2, 2020
Messages
437
Office Version
  1. 365
Platform
  1. Windows
Try changing 'For Each rw In sh.Rows' to 'For rw = 1 to RowCount'
 
Solution

BlackThorne

New Member
Joined
Nov 16, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Yes, with addition of code that deletes rows just used and manually setting up the count it works. Perhaps a little rough on the edges, but it will work for what we need.
Thank you very much :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,043
Messages
5,835,099
Members
430,341
Latest member
kelstnmate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top