To this file I added the code I want to open that test xlsx file and do the same
Renaming duplicate column names
- SUBHASHn
- Thread is marked as Resolved.
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
I am having trouble opening the file you posted. Could you please post it again?
-
-
The file paths are contained in a different workbook. Is this correct? Will you have multiple file paths for multiple workbooks on which you want to run the macro or only one path and workbook at a time?
-
In the last file your shared, the data is in column A. In the original file, the data was in column G. That is why the macro doesn't work. What I need is a copy of the actual workbook, not a sample and a copy of the workbook which contains the file paths.
-
-
We seem to be going around in circles. Do you have multiple files in "Z:\" on which you want to run the macro or only one file?
-
Since the file.xlsx has to be opened by the macro, you will have to place the macro in another workbook and run it from there.
Code
Display MoreSub ReName() Application.ScreenUpdating = False Workbooks.Open Filename:="Z:\file.xlsx" Dim LastRow As Long, rng As Range, key As Variant, x As Long LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row arr = Range("A2:A" & LastRow).Value Set dic = CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr, 1) If Not dic.Exists(arr(i, 1)) Then dic.Add arr(i, 1), Nothing End If Next i For Each key In dic.keys Range("A1").AutoFilter Field:=1, Criteria1:=key For Each rng In Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible) If x = 0 Then rng.Offset(, 1) = rng x = x + 1 Else rng.Offset(, 1) = Left(rng, WorksheetFunction.Find(".", rng) - 1) & "_" & x & ".pdf" x = x + 1 End If Next rng x = 0 Next key Range("G1").AutoFilter Application.ScreenUpdating = True End Sub -
-
Yes. Just place the line of code at the end of the macro.
-
You are very welcome.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!