Help With Macro To Move Files To Subfolder

April 25, 2019 at 12:48:32
Specs: Windows 7
Hi I have a macro shown below and it is placed in the file called " new summary".

The issue I have is that because its in summary it wont let me copy to the new folder.

Another thing im trying to do is when the new folder has been created and all files moved I need the links amending so that they go to the files within the new folder.

What I mean is when I move the files to the new folder weeks 1-4 are linked to summary via copy/paste special/links but the links stay with T:/ passacc etc and I need it to copy and link to the same thing but in subfolder that created in backup folder.

This is because the files in "T" drive will be cleared and reused.

Another thing is the backup folder will have a different name every month.

Any Ideas?

Current macro below.

Sub test()
Dim sName As Variant, bFolder As String, dest As String

sName = InputBox("Subfolder name")
If sName = "" Then Exit Sub

bFolder = "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\backup"

If Dir(bFolder & "\" & sName, vbDirectory) = "" Then
MkDir bFolder & "\" & sName
End If
dest = bFolder & "\" & sName & "\"

FileCopy "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\NEW SUMMARY\NEW SUMMARY.XLS", dest & "NEW SUMMARY.xls"
FileCopy "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\NEW INPUT SCREENS\WEEK 1", dest & "WEEK 1.xls"
FileCopy "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\NEW INPUT SCREENS\WEEK 2", dest & "WEEK 2.xls"
FileCopy "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\NEW INPUT SCREENS\WEEK 3", dest & "WEEK 3.xls"
FileCopy "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\NEW INPUT SCREENS\WEEK 4", dest & "WEEK 4.xls"

End Sub

message edited by GDB

See More: Help With Macro To Move Files To Subfolder

April 26, 2019 at 06:09:41
2 posting tips:

First, when posting in a Help forum such as this, please try to use a Subject Line that tells us something specific about your question. If everyone used a generic Subject Line such as "Help With A Macro" we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.

Second, please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.


Office Forum Moderator

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Report •
Related Solutions

Ask Question