Copy and Paste Folder to other folders using VBA

Simon Raine
Simon Raine used Ask the Experts™
on
I have a folder called "contractor_subfolders". I want to copy this folder then paste the same folder into other folders within the same directory based upon values in excel.

i.e copy and paste the folder called "contractor_subfolders" to all folders marked as contractor and not other or director. Please see screen shot.

Thanks

Simonsnip.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Well, you can manipulate files and folders using the FileSystemObject in VBA.. but your describtion of what you like to do is pretty vague, at least I don't really understand it. So can you rephrase it?

Author

Commented:
Hi

I'd like to copy the folder "contractor_subfolders" then paste into folders listed in column A where column B  = 'contractor",

hopefully screen shot helps...
ste5anSenior Developer

Commented:
E.g. select your range and then this would do it:

Option Explicit

Public Sub test()

  CopyFolder "C:\Temp\TemplateFolder\", "C:\Temp\Documents\", Selection

End Sub

Public Sub CopyFolder(AFolderToCopy As String, ABasePath As String, AControlRange As Excel.Range)

  Dim ControlRow As Excel.Range
  Dim FileSystemObject As Scripting.FileSystemObject
    
  Set FileSystemObject = New Scripting.FileSystemObject
  For Each ControlRow In AControlRange.Rows
    If ControlRow.Cells(, 2).Value = "Contractor" Then
      FileSystemObject.CopyFolder AFolderToCopy, ABasePath & ControlRow.Cells(, 1).Value
    End If
  Next ControlRow

  Set ControlRow = Nothing
  Set FileSystemObject = Nothing

End Sub

Open in new window

It requires error handling and you may chose a different approach of handing over the control range, e.g. as named list object.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Author

Commented:
How do I set the range? The range is A:B, where A is not blank
ste5anSenior Developer

Commented:
As I said, you need to decide it..

Author

Commented:
The range is A:B, how do I put that into the VBA?
ste5anSenior Developer

Commented:
As every range.. SheetReference.Range("A:B"). But the range is obviously not A:B.

Author

Commented:
Where does that go in your vba? thanks
ste5anSenior Developer

Commented:
The third parameter.
decided not to try and copy and paste, but approach the problem differently

Author

Commented:
thanks for help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial