To this file I added the code I want to open that test xlsx file and do the same
Renaming duplicate column names
-
SUBHASHn -
November 20, 2020 at 5:53 PM -
Thread is marked as Resolved.
-
-
-
And the original file contains the cell values as below
-
I am having trouble opening the file you posted. Could you please post it again?
-
And the original file contains the cell values as below
-
-
-
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?
-
The file path I was able to open but the code is not working for the file which I shared
-
Yes the file path is in different folder..
-
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.
-
I have changed the code as per column, and the original file I cannot share as it is confidential... That's the reason I shared the sample
-
-
And the workbook path is fixed as " Z:\ input file"
-
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?
-
Only one file with a name input file.xlsx
-
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
-
Let me try this...Thank you for your time
-
-
It worked well...To save the input file.xlsx can I add code like Filename.Save????
-
Yes. Just place the line of code at the end of the macro.
-
Cool, Thanks alot... Really appreciate your help
-
You are very welcome.
-
Getting this error in line 15
-
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!