VBA Macro help please

[JohnRob83]

hi all,

please can someone help me?

i have a file called InvoiceTemplate.xlsx which is an invoice. i have a separate file called InvoiceData.xlsx which contains multiple rows of data. id like a macro that would save the Invoice Template multiple times(50 times) loop until finished, update certain cells and rename the file based on the data from InvoiceData.xlsx

i have spent many hours trying to find a solution with no luck.

InvoiceData.xlsx contains as follows:

• Cell A4 - File name - to be used to save the template file as
• Cell B4 - Invoice number
• Cell C4 - Invoice date
• Cell D4 - Net amount

InvoiceTemplate.xlsx contains

• Cell H9 - Invoice number
• Cell H8 - Invoice date
• Cell G5 - Net amount

both files are on a server but for testing you can assume they are both saved in C:\manual invoices\test

any help would be greatly appreciated, i have used macros before but for formatting and i am really struggling with this.

thanks
JohnRob83

 

[NdNoviceHlp]

Just to be clear, do U want to create 50 new files from the "template" every time the macro is run? Your .xlsx files is not an actual template as that requires a .xlts or .xltm file extension. Anyways, as a general outline, do you want to populate the "InvoiceTemplate" wb with info from the "InvoiceData" wb then save the updated "InvoiceTemplate" wb as a new file (named within the "InvoiceData" file)? Are there multiple "InvoiceData" wb files or one file with all the info? If it's only 1 file, then where is all the info located? It can't all be in the single cells that U listed (ie Cell A4, B4, C4 and D4). Hope this gets U started. Dave

 

[JohnRob83]

hi,

thank you for the reply, i just need it to loop until it finds the first blank cell. could be 10 files to create, could be 100. yes to populate the "invoice Template" wb from the invoice data wb with the new file name from the invoice data tab. all invoice data will be on one file, starting from line 4 onwards. eg A4-A100, B4-B100. Line 3 are the titles/headers.

thanks
John

 

[NdNoviceHlp]

Hi *JohnRob83. You can trial this code. It seems to work for my limited testing. I changed the Invoicedata wb to a .xlsm and ran the code from there. If you want to run the code from a completely separate wb that will be different. HTH. Dave

Sub Test()
Dim xlobj As Object, FileNm As Workbook, LastRow As Integer, Cnt As Integer

On Error GoTo Erfix
Application.ScreenUpdating = False
Set xlobj = CreateObject("Scripting.FileSystemObject")

With Sheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

For Cnt = 4 To LastRow
Set FileNm = Workbooks.Open("C:\manual invoices\test\InvoiceTemplate.xlsx")
FileNm.Sheets("Sheet1").Range("H" & 9).Value = ThisWorkbook.Sheets("Sheet1").Range("B" & Cnt).Value
FileNm.Sheets("Sheet1").Range("H" & 8).Value = ThisWorkbook.Sheets("Sheet1").Range("C" & Cnt).Value
FileNm.Sheets("Sheet1").Range("G" & 5).Value = ThisWorkbook.Sheets("Sheet1").Range("D" & Cnt).Value
FileNm.Close SaveChanges:=True
'object.copyfile,source,destination,file overright(True is default)
xlobj.CopyFile "C:\manual invoices\test\InvoiceTemplate.xlsx", _
        "C:\manual invoices\test\" & CStr(ThisWorkbook.Sheets("Sheet1").Range("A" & Cnt).Value) & ".xlsx", True
Next Cnt

Erfix:
If Err.Number <> 0 Then
MsgBox "Error"
End If
Set xlobj = Nothing
Application.ScreenUpdating = True
End Sub

 

[NdNoviceHlp]

Trial #2. This is better. Dave

Sub Test()
Dim xlobj As Object, FileNm As Workbook, LastRow As Integer, Cnt As Integer

On Error GoTo Erfix
Application.ScreenUpdating = False
Set xlobj = CreateObject("Scripting.FileSystemObject")

With Sheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

Set FileNm = Workbooks.Open("C:\manual invoices\test\InvoiceTemplate.xlsx")
For Cnt = 4 To LastRow
FileNm.Sheets("Sheet1").Range("H" & 9).Value = ThisWorkbook.Sheets("Sheet1").Range("B" & Cnt).Value
FileNm.Sheets("Sheet1").Range("H" & 8).Value = ThisWorkbook.Sheets("Sheet1").Range("C" & Cnt).Value
FileNm.Sheets("Sheet1").Range("G" & 5).Value = ThisWorkbook.Sheets("Sheet1").Range("D" & Cnt).Value
FileNm.Save
'object.copyfile,source,destination,file overright(True is default)
xlobj.CopyFile "C:\manual invoices\test\InvoiceTemplate.xlsx", _
        "C:\manual invoices\test\" & CStr(ThisWorkbook.Sheets("Sheet1").Range("A" & Cnt).Value) & ".xlsx", True
Next Cnt
Application.DisplayAlerts = False
FileNm.Close SaveChanges:=False
Application.DisplayAlerts = True

Erfix:
If Err.Number <> 0 Then
MsgBox "Error"
End If
Set xlobj = Nothing
Application.ScreenUpdating = True
End Sub

 

[JohnRob83]

hi Dave,

thank you for your time, it really is appreciated.

this code is opening the file "invoiceTemplate.xlsx" saving it and then copying it without a name. It doesnt seem to be using the open file that im running the macro from "InvoiceData.xlsm" to update invoice number, invoice date, Net amount or the name to save the file as.

any ideas?

thanks

 

[NdNoviceHlp]

Did you change the sheet name to suit? Dave

 

[JohnRob83]

both sheet names are sheet1 on both workbooks. if i change the saving of the file name to activeworkbook it will save the file name but the data inside is blank. obviously i cant use activeworkbook for the data because it has opened the template and so that is now activeworkbook

 

[NdNoviceHlp]

Hmmm. My trial went like this. I created the folder address "C:\manual invoices\test" and placed the 2 files in it ("InvoiceTemplate.xlsx" and "Invoicedata.xlsm"). I placed the above code in the Invoicedata.xlsm file and ran it. It produced copies of the "InvoiceTemplate.xlsx" wb with the info as required from the "Invoicedata.xlsm" wb and named by the Sheet1 A4 to A & lastrow data as entered in the Invoicedata.xlsm file. Not sure what's going on for you? Dave

 

[JohnRob83]

i did that too, i started again with dummy files i had just created. its like it doesnt know i have a file open that it needs to use. i will look again in the morning with fresh eyes.

thank you for the help.