Copy and rename folder

D

daniel chen

Everyday, I update all the books in the Mainfolder.
Then, I make a copy of the Mainfolder and rename it mmddyyfolder.
I scripted a macro to do the updatings, but don't know how to script a
macro that will make a copy and rename it.
Please help, Thanks
 
A

Andy

Hi Daniel,

The code below was captured in a recorded macro. You can try this out and see if
it is what you are looking for.

Sub CopyAndRenameSheet()

Sheets("Sheet2").Select
Sheets("Sheet2").Copy After:=Sheets(6)
Sheets("Sheet2 (2)").Select
Sheets("Sheet2 (2)").Name = "NewSheetName"

End Sub
 
D

Dave Peterson

Can I make an arbitrary suggestion?

Instead of renaming it to mmddyyfolder, rename it to Mainfolder_yyyymmdd.

It'll sort nicely in windows explorer and make things easier to find (well,
maybe!).

This should do it:

Option Explicit
Sub testme()


Dim myOldFolder As String
Dim myNewFolder As String

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

' Dim FSO As Scripting.FileSystemObject
' Set FSO = New Scripting.FileSystemObject

myOldFolder = "C:\my documents\excel\test"
myNewFolder = myOldFolder & Format(Date, "_yyyymmdd")

If FSO.FolderExists(myOldFolder) Then
If FSO.FolderExists(myNewFolder) = False Then
FSO.CopyFolder Source:=myOldFolder, _
Destination:=myNewFolder
Else
MsgBox "Not Copied!"
End If
End If

End Sub

If I set a reference (Tools|References) to microsoft scripting runtime, then
I could use those commented lines (dim & Set) and comment the two lines above
them.
 
D

daniel chen

Thank you, Dave
This is new to me. Let me study it a bit. I will let you know how I do.
 
D

daniel chen

Hi, Dave
It's great. That is just what I wanted.
Just before I received your script, I got it running.
Basically, I formatted a datecode mmdd in cell D1, then MD "parentpath" &
Cells(1,4) & "folder".
Saved the updated file twice; first as it, second in mmddfolder.
Then closed it, went on to loop the next file and so on.
Now, with your script, I am able to cut more than 90% runtime in creating
folder_yymmdd.
It was fortunate that my other programs could adapt the change with some
minor alterations.
Thank you very much.
 
Top