Create new workbooks from template based on sheet with data

NoSparks · #1 03-08-2019, 09:38 AM

I think you could get the data directly from the table

Code:

Sub Create_Workbook_From_Template_and_Save()

    Dim rng As Range, cel As Range
    Dim Flname As String
    Dim Rev As String
    Dim Title As String
    Dim Subcontractor As String

Set rng = Workbooks("CRS Data Input").Worksheets("Data").ListObjects("Table_query").ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeVisible)

For Each cel In rng
    Flname = cel.Value
    Rev = cel.Offset(, 4).Value
    Title = cel.Offset(, 3).Value
    Subcontractor = cel.Offset(, 7).Value

    'Open new workbook from template
    Workbooks.Open FileName:=ThisWorkbook.Path & "\CRS Template.xlsx", Editable:=False
    
    'Copy data from data sheet to template
    Workbooks("CRS Template").Worksheets("CRS").Range("K1") = Flname
    Workbooks("CRS Template").Worksheets("CRS").Range("N1") = Rev
    Workbooks("CRS Template").Worksheets("CRS").Range("K2") = Title
    Workbooks("CRS Template").Worksheets("CRS").Range("K3") = Subcontractor
    
    'Save template in Output folder with custom filename
    Workbooks("CRS Template").Close SaveChanges:=True, FileName:=ThisWorkbook.Path & "\Output\" & Flname & "-CRS.xlsx"
Next cel

End Sub

a couple of links for more insight into tables:
https://www.thespreadsheetguru.com/b...t-excel-tables
https://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp

Jelmer · #2 03-12-2019, 02:14 AM

Thank you very much, NoSparks! Now it stops when it's out of visible rows

However, the data for Flname is a hyperlink in the Data sheet and should also be a hyperlink when the template is saved as new workbook. Is this possible?

I tried

Code:

Dim Flname As Hyperlink

but that didn't work, unfortunately.

EDIT:
To be more clear, the hyperlink shows as 1234-5678 but links to for example google.com/1234-5678. The cell in the template should still read 1234-5678 but the link should be still the same. This is important as the filename is based on the contents of this cell.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy data from one sheet to another based on a certain criteria	shina67	Excel Programming	2	12-28-2016 07:32 AM
Mailmerge create a coloured box based on data	haydencohen	Mail Merge	1	11-25-2015 03:32 PM
Create custom quote based on template	torma156	Word	3	09-06-2015 11:04 AM
Create a Bar with a slider that move based on data	jgallet	Excel	8	08-21-2015 03:41 PM
How to create a data validation based on another cell's value??	cfreezy	Excel	1	06-18-2015 09:51 AM