Forum Discussion
Changing file name
This is a standard macro, so it goes into a standard module, Corrected version:
Sub import()
Dim wshCSV As Worksheet
Dim wshXLS As Worksheet
Set wshXLS = ThisWorkbook.Worksheets("Hand Backs")
wshXLS.Unprotect
Set wshCSV = ActiveSheet
wshCSV.Range("D2:D60").Copy Destination:=wshXLS.Range("A5")
wshCSV.Range("L2:L60").Copy Destination:=wshXLS.Range("B5")
With wshXLS.AutoFilter.Sort
.SortFields.Clear
.SortFields.Add2 Key:=wshXLS.Range("J4")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
wshXLS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
When I click on the macro button on the handouts spreadsheet it doesn’t activate the csv file so doesn’t copy across, any way around this?
HansVogelaar thanks you're the quick responses.
I tried to send a video of explaining what I'm trying to achieve but unfortunately it was too big.
I would love to use the folder option but more than 1 person will be using the document. when the document comes to our emails it saves deep into the work system in a temp folder and looking at everyone's file paths, they look different as well.
I think I will be able to get them to use alt F8. thank you so much for your help. this means so much and going to save a lot of people time and energy.
Regards
John
That would have been possible with the earlier version that prompted the user to open the csv file, but you didn't want that.
With the current setup, where the csv file is the active workbook, that is too complicated.
- Just to make it easier for others to use is there another way where we don’t have to press alt F8? I would love to press a button with a macro attached and it would open or something similar
If you run the macro while the workbook with the macro is the active workbook, we don't know how to access the csv file, since we don't know its name.
So activate the csv file, then press Alt+F8 to activate the Macros dialog.
Select the import macro in the list and click Run.