KIM Chanthan
Member
Hi,
I have one issue to loop each workbook and update the data every month.
I have tried to write one code, but it could only update 1 cell by single formula.
So there are 4 cells (For one month), i need to write 4 formula. So going along with this kind of formula, i might need to create 4 * 12 = 48 formula for update one year data. Below is my code:
--------------------------------------
>>> use code - tags <<<
-----------------------
I also attached my files here. So that you might understand my issue clearly.
I am very basic with VBA.
Thanks,
I have one issue to loop each workbook and update the data every month.
I have tried to write one code, but it could only update 1 cell by single formula.
So there are 4 cells (For one month), i need to write 4 formula. So going along with this kind of formula, i might need to create 4 * 12 = 48 formula for update one year data. Below is my code:
--------------------------------------
>>> use code - tags <<<
Code:
Sub EmbedDATA()
Set ws = Workbooks("Value File.xlsm").Worksheets(1)
'Open Directory and Listing all File
directory = ActiveWorkbook.Path & "\"
Filename = Dir(directory & "*.xlsb")
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
'Extract all File Listing
Do While Filename <> ""
' Open workbook by filename
Workbooks.Open (directory & Filename)
Worksheets("INPUT").Select
Client = Range("B1").Value & Range("A7")
Spouse = Range("B1").Value & Range("A8")
ProductA = Range("B1").Value & Range("A9")
ProductB = Range("B1").Value & Range("A10")
Range("B7") = Application.WorksheetFunction.VLookup(Client, ws.Range("A:O"), 4, 0) * Range("B5").Value
Range("B8") = Application.WorksheetFunction.VLookup(Spouse, ws.Range("A:O"), 4, 0) * Range("B5").Value
Range("B9") = Application.WorksheetFunction.VLookup(ProductA, ws.Range("A:O"), 4, 0)
Range("B10") = Application.WorksheetFunction.VLookup(ProductB, ws.Range("A:O"), 4, 0)
Workbooks(Filename).Save
Workbooks(Filename).Close
Application.AskToUpdateLinks = True
Application.DisplayAlerts = True
'Move to next filename
Filename = Dir
Loop
End Sub
I also attached my files here. So that you might understand my issue clearly.
I am very basic with VBA.
Thanks,
Attachments
Last edited by a moderator: