Not sure if the code below give the result as you expected
The code will ask the user to type what number he wants to have the data to fill the template sheet
Assumed that the user type number 101, then the code find that number in column A sheet Sheet1,
then the code make some variables with a value based on the found cell (in this case, 101 is in cell A3 Sheet1)
Next,
the code fill sheet Template with those variables value
copy Sheet Template to a new workbook
save the new workbook as pdf and xlsx file to directory D:/test/ (drive D, folder test) with a name from cell A9 value of Sheet Template
close the active workbook
So this code create the new file just one at a time.
I'm not sure if what you mean is to create the new file just one at a time,
or you want the code create the new files as many as the number exist in column A of Sheet1.
And if you want to create the two new files (based on sheet Template) from all the existing number in column A sheet 1,
I think it will take times for the code to run.
Below the code loop to each existing number in column A sheet 1 (starting from cell A2).
Another way is to put a vlookup formula on the sheet Template,
based on cell A9 (the number) of sheet Template.
So the code will just fill number value to cell A9 (sheet Template) based on each number exist in column A (sheet Sheet1),
then the code save it as pdf and xlsx file.
No need a variable here, but you need to write the vlookup formula in each needed cell of sheet Template first.
Bookmarks