Copying sheet from file round 2!!!

SoMany · #1 01-10-2021, 07:38 AM

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"

p45cal · #2 01-11-2021, 10:11 AM

try changing:

Code:

    filename = ThisWorkbook.Path & Application.PathSeparator & "TestImportFile1.xlsx"

to the likes of:

Code:

    filename = "C:\Users\John Smith\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"

SoMany · #3 01-11-2021, 11:03 AM

Quote:

Originally Posted by p45cal

try changing:

Code:

    filename = ThisWorkbook.Path & Application.PathSeparator & "TestImportFile1.xlsx"

to the likes of:

Code:

    filename = "C:\Users\John Smith\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"

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.

Artik · #4 01-23-2021, 09:02 AM

Quote:

Originally Posted by p45cal

try changing:

Code:

    filename = ThisWorkbook.Path & Application.PathSeparator & "TestImportFile1.xlsx"

to the likes of:

Code:

    filename = "C:\Users\John Smith\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"

Hi p45cal. I do not recommend using such a "rigid" solution when referring to Desktop. For me, the path to Desktop is:
"C:\Users\<prifile_name>\SkyDrive\Pulpit". (Polish version

)
I rather recommend it:

Code:

Sub AAA()
    MsgBox GetDesktopPath & "Test import file\DifferentFolder\TestImportFile2\"
End Sub


Function GetDesktopPath()
    Dim WSHShell    As Object

    Set WSHShell = CreateObject("Wscript.Shell")
    GetDesktopPath = WSHShell.SpecialFolders(4)
    If Right(GetDesktopPath, 1) <> Application.PathSeparator Then
        GetDesktopPath = GetDesktopPath & Application.PathSeparator
    End If
End Function

Artik

SoMany · #5 02-26-2021, 10:19 PM

This has been amazing help everyone. One last thing. When pasting/copying into another sheet, I keep getting a message that says the information I want to post is large and wants to know if I want to paste the information into another clipboard later.

Any way to get the the macro to automatically click yes?

p45cal · #6 01-11-2021, 11:42 AM

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

SoMany · #7 01-12-2021, 06:11 AM

Quote:

Originally Posted by p45cal

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"....

p45cal · #8 01-12-2021, 08:44 AM

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

SoMany · #9 01-12-2021, 02:35 PM

Quote:

Originally Posted by p45cal

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

Sorry about that

p45cal · #10 01-12-2021, 03:15 PM

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

SoMany · #11 01-14-2021, 11:01 PM

Quote:

Originally Posted by p45cal

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?

Purfleet · #12 01-15-2021, 01:48 AM

Hi SoMany

Try uzer = Environ("Username")

before the filename assign

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

SoMany · #13 01-21-2021, 07:45 PM

Quote:

Originally Posted by Purfleet

Hi SoMany

Try uzer = Environ("Username")

before the filename assign

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

That did the trick. Thank you.

p45cal · #14 02-27-2021, 07:54 AM

I'm not sure if this will work.
Step through your code with F8 on the keyboard until the message pops up and make a note of which line caused it, then edit the macro to include the line:

Code:

Application.DisplayAlerts = False

just before it, and later add the line:

Code:

Application.DisplayAlerts = True

to restore it so that future alerts appear as normal (this applies to the user interface messages too, such as when deleting a sheet manually).

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copying a Sheet in a Workbook to Another with Destination-Need Values Instead of Formulas	rsrasc	Excel Programming	4	08-19-2018 04:00 PM
Copying a part of a docx file as a separate file	officeboy09	Word	6	09-26-2014 05:15 PM
Copying excel charts into word - huge file size.	Matt.M	Word	2	04-02-2014 07:38 PM
Copying data from sheet with deleted columns creates blanks	ZGreyArea	Excel	1	11-20-2013 10:12 AM
Error Copying file or folder - Drag-N-Drop--> Network Shared	Kris.Fulgham	Outlook	1	10-17-2005 09:02 AM