Selected Answer
Michael01, I was going to put it as an comment but I used more than 1500 characters, Don please forgive me I know that this should be a comment
I'm just going to help you with this code that's why I didn't put an answer
First, please use CODE button and replace Code_Goes_Here with your code
(I will not delete but I will make them comments by put ' before them)
Sub wbnme_wsnme()
'you used this variable
Dim lastrw as integer
Dim fName As String
'you don't need this
'Dim wrksht As String
Dim path As String
Dim strFile As String
' for better speed turn off screen but make sure calculation updates are automatic
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.DisplayStatusBar = False
.EnableEvents = False
.AlertBeforeOverwriting = False
End With
'you did that before so just delete these
' Application.ScreenUpdating = False
' Application.DisplayAlerts = False
' Application.EnableEvents = False
On Error Resume Next
' open worksheet with path and filename, why you closed open workbooks?
' Workbooks.Close
Workbooks.Open ("Q:\Q316\Data\FilesInFolder.xlsx"), UpdateLinks
'Selecting anything in vba will make the macro slower so just Replace selecting and activate with the sheet like this, and use sheets (1) so you indicate the sheet by it's number so you could change sheet name later .
' Sheets("Sheet1").Select
With Sheets(1) 'ActiveSheet
lastrw = .UsUsUsedRange.Rows.Count
'you don't need this too
' lastcol = '.UsedRange.Columns.Count
'You don't need to make i=2 because you typed it in the For, I also put . Before the range so that range refer to that sheet (that you typed after with)
' i = 2
For i = 2 To lastrw
path = .Range("$B" & i).Value
fName = .Range("$A" & i).Value
'I didn't understand what this will help
' If .Range("B" & i).Value <> '.Range("B" & i = 1).Value Then
' iend = i
' End If
'you should put the full name with the extention because there are more than one
strFile = path & fName '& ".xlsx"
With Workbooks.Open(strFile, UpdateLinks:=False)
'You did that too
' With 'Workbooks.Open(strFile, 'UpdateLinks:=False)
'another . Here, and wrksht is null and you don't need it so replace it with fName
.Worksheets(1).Name = fname 'wrksht this assumes that you want to rename sheet 1
ActiveWorkbook.Save
ActiveWorkbook.Close
End With
Workbooks("Q:\Q316\Data\FilesInFolder.xlsx").Worksheets("Sheet1").Activate
'the same just put 1 so you could change name later.
Sheets(1).Select
End With
Next
End With
Workbooks.Close
' reset to default
With Application
.AlertBeforeOverwriting = True
.ScreenUpdating = True
.DisplayAlerts = True
.DisplayStatusBar = True
.EnableEvents = True
End With
'the same you just did that
' Application.ScreenUpdating = True
' Application.DisplayAlerts = True
' Application.EnableEvents = True
End Sub
And to make this macro work you need to make that file have all workbooks that you want edit
Q:\Q316\Data\FilesInFolder.xlsx
(And don't forget to change this path to your needs)
In Column A the path to file and in Column B file names starting from the row 2.
So this code will work (if you made that file)
Please Michael01 edit your answer because I'm going to delete this answer,
And if all the files are in the same folder (don asked you about that) I could help you with a macro to make that file