VBA Help to create excel workbook with template

[bergman99]

Hi folks,

I've got a data table with 20-25 columns worth of data (sheet1), using an index number I use Index and Match to pull all of the relevant data from the table onto a formatted worksheet into the "fields" on the template (sheet2).

What I would like, is to be able to have a third worksheet where if I paste 500 index numbers (from sheet1) into A1:A500, and had a "print" button that pasted A1 into my template (sheet2) so that my index and match formulas pull their data for the number that exists in A1, then carries on for A2:A500.

The "print" action that happens would be the following, this is the part I have no clue how to do...

1. Paste the value in A1 on sheet3 to sheet2 template on AA1
2. Copy sheet2 to a new workbook, as values (excel has to calculate here first, to update my formulas).
3. Save the new workbook, named after sheet3 A1. The file directory could be pulled from sheet3 AA2.
4. Paste the value from sheet3 A2 into sheet2 AA1
5. Copy sheet2 to a new workbook...
6. Rinse and repeat until there is no more data, IE A1:A20 (sheet3) have index numbers, they get "printed to file", if there's no data below that then the program stops.

I would very much appreciate some help in this. If someone is feeling super patient I would love a breakdown on how it works, VBA to me is a confusing monster.

 

[Coding4Fun]

What I would like, is to be able to have a third worksheet where if I paste 500 index numbers (from sheet1) into A1:A500, and had a "print" button that pasted A1 into my template (sheet2) so that my index and match formulas pull their data for the number that exists in A1, then carries on for A2:A500.

What would you be pasting into this 3rd sheet in Range A1:A500? Can you also provide an example of the template sheet and the index/match formula you refer to.

 

[bergman99]

What would you be pasting into this 3rd sheet in Range A1:A500? Can you also provide an example of the template sheet and the index/match formula you refer to.

My data table is a list of 2000+ items with various information such as received date, calibration certification numbers, size, etc. Every line on the data table has an "INDEX" number, a unique number between 1-2000+. The report sheet just represents the information from the data table, with each field on the sheet pulling based off the index number in AA1 (on the report page).

Does that make sense? I can't attach a sample file..

 

[Coding4Fun]

There is not enough info for me to fully understand what you’re trying to do so I will take a stab at giving you direction based on your previous criteria.

1. Paste the value in A1 on sheet3 to sheet2 template on AA1

‘ This is not a copy and paste but will make the 2 fields match 
Sheets(“sheet2”).Range(“AA1”) = Sheets(“sheet3”).Range(“A1”)
 
‘If you want to actually copy the values you can use
Sheets("Sheet3").Activate
Range("A1").Copy
Sheets("Sheet2").Activate
Range("AA1").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

2. Copy sheet2 to a new workbook, as values (excel has to calculate here first, to update my formulas).

Sheets(“Sheet2”).Activate
Calculate
Sheets("Sheet2").Copy

3. Save the new workbook, named after sheet3 A1. The file directory could be pulled from sheet3 AA2.

Dim Filename As String 
Dim FilePath As String
FileName = Range(“A1”).Value
Filepath = Range(“AA2”).Value
ActiveWorkbook.SaveAs (Filepath & “\” & FileName & “.xlsx”)

4. Paste the value from sheet3 A2 into sheet2 AA1

You can use a For Loop to go through all of the instances

OnRow = 1
 
For I = 1 to (Number Of Rows)
 
Enter the code to run for each loop here
 
Next I

5. Copy sheet2 to a new workbook...


6. Rinse and repeat until there is no more data, IE A1:A20 (sheet3) have index numbers, they get "printed to file", if there's no data below that then the program stops.

You can use this to capture the number of rows in a sheet to use in your loop

LR1 = Cells(Rows.Count, "A").End(xlUp).Row
OnRow = 1
For I = 1 to LR1
 
Sheets("Sheet3").Activate
Range("A" & OnRow).Copy
Sheets("Sheet2").Activate
Range("AA1").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Sheets(“Sheet2”).Activate
Calculate
Sheets("Sheet2").Copy
 
Dim Filename As String 
Dim FilePath As String
FileName = Range(“A” & OnRow).Value
Filepath = Range(“AA2”).Value
ActiveWorkbook.SaveAs (Filepath & “\” & FileName & “.xlsx”)
 
Next I