Solved: VBA Worksheet Project

[exwarrior187]

I've been tasked with creating (66) instances of the same worksheet with unique values for (66) entries.

Currently the list of values and it's corresponding data sits in the "list" worksheet, the form is the "template" worksheet.

The template is populated based on the contents of cell C70. The cells highlighted in yellow are populated through 'vlookups' off of C70.

Essentially what I would like to do is loop through the list and create an instance of the "template" for each entry in the "list". Here's a twist: because of the series of 'vlookups' I need to retain the values and not the formulas from these cells, save (to a new workbook separetely or save all entries to a new workbook).

Any assistance is appreciated I understand that this could be quite a challenge!

[lucas]

Hey Tex,
Try the attached file. Run the sub SplitUsingDatabase

It doesn't copy them to a new workbook yet but you have to do some of the work.......you might use something like the following to achieve the goal of copying them to a new workbook....

it copies to the excel default directory as filename: TestItOut.xls

Option Explicit
Sub CopySheetsNewbook()
Application.ScreenUpdating = False
Dim ThisBook As Workbook
Dim WkSht As Worksheet, NewBook As Workbook
Set ThisBook = ThisWorkbook
Set NewBook = Workbooks.Add(xlWBATWorksheet)
For Each WkSht In ThisBook.Worksheets
Select Case WkSht.Name
Case "A_datanew", "A_General", "A_ISPACEMONTH", "A_ISPACEYEAR"
'these are the sheets names which shouldn't be copied
Case Else
WkSht.Copy After:=NewBook.Sheets(NewBook.Sheets.Count)
End Select
Application.CutCopyMode = False
Next WkSht
Application.DisplayAlerts = False
Worksheets("Sheet1").Delete

NewBook.SaveAs Filename:="TestItOut.xls"
End Sub
let me know if you need clarification on the array....or if this even helps...


Edit: The H1's in the array are cells in the row that you did not use. I sent them to an area outside your form(H1) and added one blank at the end to clear the cell H1....

[exwarrior187]

Lucas,

Thanks for the reply! Works as discussed with one little hiccup. The cell which the Vlookups feed off of has to be populated as the first step so that then the sub copys/pastesvalues the values will be correct. So i need to pass each value in the first column of my "list" sheet to the "template" sheet at cell "C70" and then let it calculate/copy/pastevalues. I think I could stumble through that on my own but I'm sure I would break some other part of the code. After that wrinkle is ironed out my task will be satisfied.

P.S. I have another Macro to separate the sheets so don't pay that any attention.

Thanks,
Ex

[lucas]

Not sure I follow but I will give it a shot.....

You need the template sheet to remain the way it is so you can use the vlookups.....

Why not just add another sheet that is not named template and use it for your vlookups and keep the template sheet just for the code I provided?

the template sheet could be hidden......unhide it to copy and add data, then rehide it at the end.

I hope I'm understanding your problem.

unless you are saying that you need to do calculations on the template sheet and copy the data from there.......in which case I would suggest that all calculations be done on the list sheet and then can be retrieved to the appropriate sheet and cell using the code I provided.......

[exwarrior187]

Wow so far this works great, I can't thank you enough for shaving days off my current workload. Now I can use that time to peruse more vba examples on the forum :)