How Change VBA Code from cell value to cell value with path 2

[Nogi]

I all,

I have a question regarding this script

Sub wrapper2()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("folderlist").Cells(x, 1) <> ""
v = InStrRev(Sheets("folderlist").Cells(x, 1), "\")
dest = ActiveWorkbook.Path & Mid(Sheets("folderlist").Cells(x, 1), v, 99)
Set f = fs.getfolder(Sheets("folderlist").Cells(x, 1))
f.Copy dest
' f.Delete: 'optional step
x = x + 1
Wend
fs = ""
End Sub

What it does is read the paths entered in the cells in Column A in excelsheet "folderlist" , and copy it to the current path of the excel where this script is on.

As example:
Cell A1 contains value c:/thispath/folder 1
Cell A2 contains value c:/thispath/folder 2
etc.

The script will copy each folder to the current path.

Now i'm not into VBA code at all, but i would like to change the script, so that the path doesn't need to be entered over and over again, but only inserting the foldernames in the column A would be enough.

So instead of my previous example, i would only have to insert
Cell A1: folder 1
Cell A2: folder 2
etc.
So the "c:/thispath/ " would be implemented in the script.

Is there somebody who can help me out with this please?

Thank you very much for your help

 

[rmikesmith]

Nogi,

Here is a modified version of your script that should do what you want:

Sub wrapper2()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("folderlist").Cells(x, 1) <> ""
dest = ActiveWorkbook.Path & "\" & Sheets("folderlist").Cells(x, 1)
Set f = fs.GetFolder("C:\thispath\" & Sheets("folderlist").Cells(x, 1))
f.Copy dest
' f.Delete: 'optional step
x = x + 1
Wend
fs = ""
End Sub

You could also use a constant in place of the hard-coded source path in the GetFolder call.

Regards,
Mike

 

[Nogi]

Yeay! It works.

Thanks alot for your help Mike, i appriciate it, really.
I only have one tiny question. This script copies the foldernames out of column A.
How do i change it so it takes the foldernames out of column B instead of column A? Normally in column A i have other information.

 

[Gavona]

cells(RowIndex, ColumnIndex)

Mike's code uses x for the RowIndex, starts with 1 and increments it by 1 as it looks at each row of the spreadsheet.
The second parameter is set to 1 throughout the code. This specifies which column to look in......


Gavin

 

[Nogi]

Hey thanks for your help Gavona. Same to you Mike.

It finally works as i've always wanted it to. Yeay!

God bless you & take care!

Nogi