VBA Code help - How do I change source and destination from a single value to a list?

[willow1985]

Sub Macro1()
'
' Macro1 Macro
'
Dim SourcePath As String
Dim DestinationPath As String
Set fso = CreateObject("Scripting.FileSystemObject")

SourcePath = Range("A1").Value 'would like every value in column A
DestinationPath = Range("B1").Value 'would like every value in column B
 
'Copy Folder
fso.CopyFolder SourcePath, DestinationPath
'
End Sub

Book1
	A	B
1	\\dav-1\data\ADMINISTRATION\Willow Documents\IN WORK\Source	\\dav-1\data\ADMINISTRATION\Willow Documents\Destination
2	\\dav-1\data\ADMINISTRATION\Willow Documents\IN WORK\Source2	\\dav-1\data\ADMINISTRATION\Willow Documents\Destination2
3	\\dav-1\data\ADMINISTRATION\Willow Documents\IN WORK\Source3	\\dav-1\data\ADMINISTRATION\Willow Documents\Destination3
Sheet1

 

[Sequoyah]

Hi @willow1985 - just add a loop to your code:

Sub Macro1()
    Dim SourcePath As String, DestinationPath As String
    Dim fso As Object
    Dim LastRow As Long, i As Long
  
    Set fso = CreateObject("Scripting.FileSystemObject")   
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    
    For i = 2 To LastRow
        SourcePath = Cells(i, "A").Value
        DestinationPath = Cells(i, "B").Value       
      
        If SourcePath <> "" And DestinationPath <> "" Then
          
            fso.CopyFolder SourcePath, DestinationPath
        End If
    Next i
  
    Set fso = Nothing
End Sub

 

[abdelfattah]

Hi,
just guessing .

Sub Macro1()
Dim SourcePath As String
Dim DestinationPath As String
Dim r As Long
Set fso = CreateObject("Scripting.FileSystemObject")
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
    SourcePath = Range("A" & r)
    DestinationPath = Range("B" & r)
'Copy Folder
fso.CopyFolder SourcePath, DestinationPath
Next r
End Sub

 

[MikeVol]

Hi @willow1985. Another option.

Option Explicit

Sub Macro1()
    Dim fso         As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim i           As Long
    i = 1

    Do While Cells(i, 1).Value <> ""

        Dim SourcePath As String
        SourcePath = Cells(i, 1).Value

        Dim DestinationPath As String
        DestinationPath = Cells(i, 2).Value

        If fso.FolderExists(SourcePath) Then
            fso.CopyFolder SourcePath, DestinationPath
        Else
            MsgBox "Source folder does not exist: " & SourcePath
        End If

        i = i + 1
    Loop

    Set fso = Nothing
End Sub

 

[willow1985]

Hi @willow1985 - just add a loop to your code:

Sub Macro1()
    Dim SourcePath As String, DestinationPath As String
    Dim fso As Object
    Dim LastRow As Long, i As Long
 
    Set fso = CreateObject("Scripting.FileSystemObject")  
   
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

   
    For i = 2 To LastRow
        SourcePath = Cells(i, "A").Value
        DestinationPath = Cells(i, "B").Value      
     
        If SourcePath <> "" And DestinationPath <> "" Then
         
            fso.CopyFolder SourcePath, DestinationPath
        End If
    Next i
 
    Set fso = Nothing
End Sub

In both codes all of the files from Source (A1) are getting transferred into Destination2 (B2). I need A1 to go to B1 and A2 to go to B2 etc.
Is there anyway to do this?

 

[Sequoyah]

Hi, just a small change to include first row, replace the line

For i = 2 To LastRow

with

For i = 1 To LastRow

 

[willow1985]

Hi, just a small change to include first row, replace the line

For i = 2 To LastRow

with

For i = 1 To LastRow

That worked!

Thank you very much!