So I've made enough progress in VBA to post this again. I'm trying to copy a sheet from another file in another folder. I can now make macros that copy data from another file within a file, but don't know how to copy the sheet when it's in another file.



Would someone modify this or say what code I should use?

The path for the other folder would be
"C:\Users\John Smith\Desktop\Test import file\DifferentFolderf\TestImportFile2"

Attached Files

MacroTest.xlsm (20.4 KB, 6 views)

try changing:to the likes of:

Ok, so that worked, but this file will be used by others and kept in their folders under different user names. I tried something like;


filename = "C:\Users" & uzer & "\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"


And it did not work in my profile. The spreadsheet must be usable in other users profiles.

filename = "C:\Users\" & uzer & "\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"

Even with the change it's not finding the folder. The new macro is below.


Edit; and for some reason I added an extra "f" in the original post in "DifferentFolderf"....

1. No macro
2. the variable uzer should contain something; is it the right thing?

Sorry about that

Attached Files

MacroTest.xlsm (20.5 KB, 3 views)

Nothing assigns any value to uzer. That's why it's not found.

I don't understand the post. What should be the code so the spreadsheet can be used from any users account?

Hi SoMany

Try uzer = Environ("Username")

before the filename assign

Or ,ideally, use a network drive location it will probably be more reliable