#1
|
|||
|
|||
Copying sheet from file round 2!!!
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" |
#2
|
||||
|
||||
try changing:
Code:
filename = ThisWorkbook.Path & Application.PathSeparator & "TestImportFile1.xlsx" Code:
filename = "C:\Users\John Smith\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx" |
#3
|
|||
|
|||
Quote:
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. |
#4
|
||||
|
||||
filename = "C:\Users\" & uzer & "\Desktop\Test import file\DifferentFolderf\TestImportFile2.xlsx"
|
#5
|
|||
|
|||
Quote:
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".... |
#6
|
||||
|
||||
1. No macro
2. the variable uzer should contain something; is it the right thing? |
#7
|
|||
|
|||
Sorry about that
|
#8
|
||||
|
||||
Nothing assigns any value to uzer. That's why it's not found.
|
#9
|
|||
|
|||
I don't understand the post. What should be the code so the spreadsheet can be used from any users account?
|
#10
|
|||
|
|||
Hi SoMany
Try uzer = Environ("Username") before the filename assign Or ,ideally, use a network drive location it will probably be more reliable |
#11
|
|||
|
|||
Quote:
That did the trick. Thank you. |
#12
|
|||
|
|||
Quote:
"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 |
#13
|
|||
|
|||
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? |
#14
|
||||
|
||||
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 Code:
Application.DisplayAlerts = True |
|
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 |