Copying sheet from file round 2!!!

p45cal · #1 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 · #2 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 · #3 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 · #4 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?

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