VBA to copy a file with look ups and convert formuals to values

[steB2]

I currently have a master spreadsheet containing loads of data, I then a a template that pulls selected data based on the file name that then poplates at the top of the spreadsheet.

I have 2 codes I have worked out through google to firstly duplicate the template 60 times based on a list and then I have a cope to convert the duplicated spreadsheet from formulas to values.

Is there any way of combining both codes into the one master spreadsheet? (Apologies in advance for code as its all through google. Total novice at this.

Duplicate Code

Sub SaveMasterAs()

'Excel 10 Tutorial

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("Master Data").Range("B2", Worksheets("Master Data").Range("B2").End(xlDown))

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

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

For Each c In rNames

With wb

'If You Need To Copy Something To the Template Use this line

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

'Path and name for copied workbook

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

End With

Set wb = ActiveWorkbook

Next c

wb.Close

End Sub

Convert Coding

Sub values()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws

End Sub

 

[rollis13]

You can choose calling your macro "values" like this:

'...
        Set wb = ActiveWorkbook
        Call values                               '<- added
    Next c
    wb.Close
End Sub

maintaining the "values" macro or inserting the four lines of code instead of the Call line and deleting the whole macro "values" no longer needed.

 

[steB2]

Hi Rollis,

I have tried adding the Call values but don't seem to be able to get this to work, I also do not understand the alternative you spoke about?

 

[rollis13]

Did you debug your macro ? what happens when you go through the line Call values ?

 

[steB2]

Not sure if doing it right, run the debug and it comes up with a message to say the expression Values and can't compile module

 

[rollis13]

Yes, somewhere you aren't doing it right. Do you have other macros in your workbook ?
Here, if you are going to use Call values, all you need is macro "SaveMasterAs" and macro "values".
Elsewise, see if you manage to attach a demo file so I can see what's going on.