Hello and thanks for looking:
I have the following
1) 15 or so excel files in a folder like the attachment BostonSPS. The only thing different is the name of the file and the number of rows can month to month. This is important because I need to have the code look to the last row (and it is not always the same row each month) of data and fill in the plandescription
They are located here: C:\Users\John\Company2) I have an excel file PlanDescLookup here: C:\Users\John\PlanDescLookUp
I want to use vba to fill in the PlanDescription in each of the groups located C:\Users\John\Company
Here is what I have so far:
Sub DEhic_Step1()
' Workbooks.Open ("C:\Users\John\PlanDescLookUp\.xlsx") ' Opens the PlanDescriptions look up table
Dim fPath As String
Dim i As Long
Dim Answer As String
Dim oFSO As Object, oFolder As Object, oFile As Object
Answer = "CurrentMonth"
fPath = "C:\Users\John\Company\"
Application.ScreenUpdating = False
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(fPath)
For Each oFile In oFolder.Files
With Workbooks.Open(oFile.Path) ' opens the 1st file in the folder that I want to update with the plan description
Sheets(Answer).Activate
With .Sheets("CurrentMonth").Activate 'CurrentMonth worksheet is now active
'This is where I think the v lookup code goes. I need to make sure that the formula gets populated in for each row that has data.
' I also want to copy that row and paste as values to remove the formula.
End With
.Save
.Close
i = i + 1
End With
Next
'turn screen back on
Application.ScreenUpdating = True
'Give feedback
MsgBox "All done." & vbNewLine & "Number of files changed: " & i, vbOKOnly, "Run complete"
End Sub
I have the following
1) 15 or so excel files in a folder like the attachment BostonSPS. The only thing different is the name of the file and the number of rows can month to month. This is important because I need to have the code look to the last row (and it is not always the same row each month) of data and fill in the plandescription
They are located here: C:\Users\John\Company2) I have an excel file PlanDescLookup here: C:\Users\John\PlanDescLookUp
I want to use vba to fill in the PlanDescription in each of the groups located C:\Users\John\Company
Here is what I have so far:
Sub DEhic_Step1()
' Workbooks.Open ("C:\Users\John\PlanDescLookUp\.xlsx") ' Opens the PlanDescriptions look up table
Dim fPath As String
Dim i As Long
Dim Answer As String
Dim oFSO As Object, oFolder As Object, oFile As Object
Answer = "CurrentMonth"
fPath = "C:\Users\John\Company\"
Application.ScreenUpdating = False
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(fPath)
For Each oFile In oFolder.Files
With Workbooks.Open(oFile.Path) ' opens the 1st file in the folder that I want to update with the plan description
Sheets(Answer).Activate
With .Sheets("CurrentMonth").Activate 'CurrentMonth worksheet is now active
'This is where I think the v lookup code goes. I need to make sure that the formula gets populated in for each row that has data.
' I also want to copy that row and paste as values to remove the formula.
End With
.Save
.Close
i = i + 1
End With
Next
'turn screen back on
Application.ScreenUpdating = True
'Give feedback
MsgBox "All done." & vbNewLine & "Number of files changed: " & i, vbOKOnly, "Run complete"
End Sub