VBA to duplicate a file with look ups and convert formula's to values

[steB2]

Good day all,

I currently have a master file with all my data, I then have the below VBA that duplicates and renames a template file that has formulas in as per my list and saves these documents in excel format.

The template that is duplicate and renamed has formuals that filters and looks up certain data based on the file name it is saved as. Currently once I have run my VBA I have to open all the documents it creates and manually copy and past all the data as values. Is there a way to my VBA so that when it duplicates and renames the file it copies/pastes the data it pulls through as a the value rather than as the formula.

I am a complete novice with VBAs and haev created it based on a youtube video and notes.

Sub SaveMasterAs()

Dim wb As Workbook

Dim rNames As Range, c As Range, r As Range

'Current file's list of names and ids on sheet1.

Set rNames = Worksheets("Orders").Range("A2", Worksheets("Orders").Range("A2").End(xlDown))

'Path and name to master workbook to open for copy, saveas.

Set wb = Workbooks.Open(ThisWorkbook.Path & "\ReturnTemplate.xlsx")

For Each c In rNames

With wb

'.Worksheets("Sheet1").Range("A1").Value = c.Offset(, 1).Value 'ID

'Path and name for copied workbook

.SaveAs Filename:=ThisWorkbook.Path & "\Return Files\" & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End With

Set wb = ActiveWorkbook


Next c

wb.Close

End Sub

 

[OaklandJim]

Without knowing more this seems doable. Unfortunately, your description of what is needed is just too vague and not complete enough for many list users to help you.

Plus, ideally, you provide a link to your workbooks (Master and Template). Otherwise, someone willing to assist might need to recreate it to develop and test the necessary code. Put the file on Box, Dropbox, 1Drive, Google Drive or Sharepoint. Provide a link to the workbook using the link icon at the top of the message box.

Consider using Mr Excel's excellent add-in called XL2BB which enables you to post a portion of a worksheet. With that add-in you can show relevant data in a usable form. That way someone willing to help you does not have to create fake data or guess what your data looks like. See XL2BB - Excel Range to BBCode for details.

GENERALLY to transfer just values use the .Value property of a range.

Dim rTargetRange As Range

Dim rSourceRange As Range

Set rSourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C30")

Set rTargetRange = Workbooks("MyWorkbook").Worksheets("Sheet1").Range("A1:C30")

rTargetRange.Value = rSourceRange.Value

 

[steB2]

Hi OaklandJim,

Many thanks for your response. I have uploaded a copy of the documents here Documents

Hope this will help, I input all the data in the return data file and then copy a list of the individual order numbers to the orders tab, once completed I run the VBA to create multiple copies of the template and renames each of them based on the list. These duplicated templates will currently look up the file name and then pull the relevant data for head heading in the template as a formula, I am looking to pull the data but paste it as a value once it has looked up the data.

Current way is working but having to open up sometimes 100 files to copy and paste is time consuming when I think a VBA can be amended to do what I need it to do just can't work out how to write the code to do it.

Total novice here.

 

[OaklandJim]

What you want sounds quite doable but I'll need more to help. Consider posting files with at least some fake data so I don't have to guess about what it looks like.

Also, be more specific about what data goes where after the copy of the template is created.

Finally, there is a link to cell(s) in some other file in the template. What file is that and what cell or range does the link refer to?

 

[OaklandJim]

Regarding...

These duplicated templates will currently look up the file name and then pull the relevant data for head heading in the template as a formula

I just could not understand this part of your post. What file name? What is "relevant data"?