Need assistance in cleaning up a macro

[ghrek]

Hi

I have the following macro and I need to tidy up and make a few changes if poss. What it is when it opens up X/BACKUP I need it to auto create a subfolder and then ask me to name it before moving the data from the other files into it.

Macro enclosed.

ActiveWorkbook.SaveAs fileName:="X:\backup\summary.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ChDir "X:\NEW INPUT SCREENS"
Workbooks.Open fileName:="X:\NEW INPUT SCREENS\WEEK 1.xls"
ActiveWorkbook.Save
ChDir "X:\backup"
ActiveWorkbook.SaveAs fileName:="X:\backup\WEEK 1.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ChDir "X:\MKEYNES"
Workbooks.Open fileName:="X:\MKEYNES\WEEK 2.XLS", updatelinks:=3
ChDir "X:\backup"
ActiveWorkbook.SaveAs fileName:="X:\backup\WEEK 2.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ChDir "X:\NEW INPUT SCREENS"
Workbooks.Open fileName:="X:\NEW INPUT SCREENS\WEEK 3.xls"
ChDir "X:\backup"
ActiveWorkbook.SaveAs fileName:="X:\backup\WEEK 3.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ChDir "X:\MKEYNES"
Workbooks.Open fileName:="X:\MKEYNES\WEEK 4.XLS", updatelinks:=0
ChDir "X:\backup"
ActiveWorkbook.SaveAs fileName:="X:\backup\WEEK 4.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
End Sub

 

[DanteAmor]

What it is when it opens up X/BACKUP I need it to auto create a subfolder and then ask me to name it before moving the data from the other files into it.

Do you want to copy the files to the new folder?
How do you want to auto create the new folder, that is, where do you get the name from?
If the new folder has already been created, do you want the macro to ask for that name?
Finally, are you just copying the file from one folder to another?

 

[ghrek]

Do you want to copy the files to the new folder? - YES

How do you want to auto create the new folder, that is, where do you get the name from?- I want it to auto create a new sub folder and it ask me to name it

If the new folder has already been created, do you want the macro to ask for that name? No as there shouldnt be a folder already created

Finally, are you just copying the file from one folder to another? Ideally yes but I need to break links in the files once moved as I will reuse the files.

 

[DanteAmor]

I'll give you an example and tell me if it's what you want.

Try this

Sub test()
    Dim sName As Variant, bFolder As String, dest As String
    
    sName = InputBox("Subfolder name")
    If sName = "" Then Exit Sub
    
    bFolder = "X:\backup"
    bFolder = "C:\trabajo"
    
    If Dir(bFolder & "\" & sName, vbDirectory) = "" Then
        MkDir bFolder & "\" & sName
    End If
    dest = bFolder & "\" & sName & "\"
    
    FileCopy "X:\NEW INPUT SCREENS\WEEK 1.xls", dest & "WEEK 1.xls"
    FileCopy "X:\MKEYNES\WEEK 2.XLS", dest & "WEEK 2.xls"
    FileCopy "X:\NEW INPUT SCREENS\WEEK 3.xls", dest & "WEEK 3.xls"
    FileCopy "X:\MKEYNES\WEEK 4.XLS", dest & "WEEK 4.xls"
    
End Sub

 

[ghrek]

bFolder = "X:\backup"
bFolder = "C:\trabajo"

Sorry just one other question before I try. Is the first bFolder where im getting data from and the 2nd one where I want data to go to?

 

[DanteAmor]

Sorry
Delete this line:

[COLOR=#333333]bFolder = "C:\trabajo"[/COLOR]

This is the destination folder
bFolder = "X:\backup"

The destination folder is created a subfolder with the name that you capture in the inputbox

the origin is in each line:

    FileCopy "[COLOR=#0000ff]X:\NEW INPUT SCREENS\WEEK 1.xls[/COLOR]", dest & "WEEK 1.xls"
    FileCopy "[COLOR=#0000ff]X:\MKEYNES\WEEK 2.XLS[/COLOR]", dest & "WEEK 2.xls"
    FileCopy "[COLOR=#0000ff]X:\NEW INPUT SCREENS\WEEK 3.xls[/COLOR]", dest & "WEEK 3.xls"
    FileCopy "[COLOR=#0000ff]X:\MKEYNES\WEEK 4.XLS[/COLOR]", dest & "WEEK 4.xls"

 

[ghrek]

spot on just what I needed. Thanks

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[ghrek]

Hi I need to add one thing to this if I can

I have amended the macro to add another folder called "summary". It all moves it to the back up file as planned but im trying to amend the links.

The document summary is linked via PASTE SPECIAL to weeks 1-4 and once ive saved them in the file called backup they are still linked to the original weeks 1-4 files in T:\Passenger Accounts\SHARED\passacc\Excel\passacc\NEW INPUT SCREENS.

What im trying to do is to break the links and just copy and paste as the original files T:\Passenger Accounts\SHARED\passacc\Excel\passacc\NEW INPUT SCREENS are going to be used again.

Please correct me if im wrong but I think that once ive moved the files I can copy and paste values in the summary folder and that will break links?

Is there a quick way of doing that as is it copy and paste values to each link?



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

 

[DanteAmor]

Do you mean to open each book, copy all the cells, paste as values, save the file and close the book?