Moving folders using VBA

[kilikanzer]

Hi,

I would like to ask your kind help with the following issue:

I have created amount of folders by this script:

[VBA]Private Sub CommandButton1_Click()

Dim R As Range
Dim RootFolder As String
RootFolder = "G:\TEAM\TEST"
For Each R In Range("B4:B1411")
If Len(R.Text) > 0 Then
On Error Resume Next
MkDir RootFolder & "\" & R.Text
On Error GoTo 0
End If
Next R
End Sub[/VBA]

So I need a macro which will move some of these folders by criteria in column C.

For example, if the text in cell C4 is "yes", the folder created by the text in B4 have to move in "G:\TEAM\TEST\positive"

If the text in cell C5 is "no", the folder created by the text in B5 have to move in "G:\TEAM\TEST\negative", etc.

Any advice, help is muchly appreciated.

Thank you !

[Bob Phillips]

[VBA]Private Sub CommandButton1_Click()

Dim R As Range
Dim RootFolder As String

RootFolder = "G:\TEAM\TEST"
For Each R In Range("B4:B1411")

If Len(R.Text) > 0 Then

If R.Offset(0, 1).Value = "yes" Then

Name R.Value As RootFolder & "\positive" & Mid$(R.Text, InStrRev(R.Text, "\"), 99)
Else

Name R.Value As RootFolder & "\negative" & Mid$(R.Text, InStrRev(R.Text, "\"), 99)
End If
End If
Next R
End Sub
[/VBA]

[kilikanzer]

Thank you!

Maybe its my bad but i have "Run-time error 5" in this row:

Name R.Value As RootFolder & "\positive" & Mid$(R.Text, InStrRev(R.Text, "\"), 99)

And i have to add that the cells in column "C" can be "yes", "no" or "empty". So i want to move only these folders (created from Range("B4:B1411")) which in Range("C4:C1411") are not "empty".

Thank you once again !

[Bob Phillips]

Give me an example of what is in column B.

[kilikanzer]

In column B there are a names (text format).

Simplified it's looking like that:

B

C

Folder name

Criteria

folder 1

yes

folder 2

yes

folder 3

yes

folder 4
folder 5
folder 6

no

folder 7
folder 8

no

folder 9

no

folder 10

yes

folder 11
folder 12

yes

folder 13

yes

folder 14

yes

folder 15

[Bob Phillips]

You need to identify where those column B folders are located so as to move them. I had assumed they would be of the form x:\xxxx\xxxx so as to have a full path to rename. What is your path?

[snb]

[VBA]

Private Sub CommandButton1_Click()
On Error Resume Next
mkdir "G:\TEAM\TEST"
mkdir "G:\TEAM\TEST\positive"
mkdir "G:\TEAM\TEST\negative"

For Each cl In Range("B4:B1411").specialcells(2)
MkDir "G:\TEAM\TEST\" & iif(cl.offset(,1)="Yes","positive\",iif(cl.offset(,1)="no","negative\","")) & cl.value
Next
End Sub

[/VBA]

[kilikanzer]

Thank you this is working well. But it don't solve the problem in fully. Because in this way the macro creates in the subdirectories new folders (same like in the main but divided by the criteria) and we have several same folders in the main and the subdirectories.

So already created (by macro1) folders in "G:\TEAM\TEST" should be moved (with all data in them) in "G:\TEAM\TEST\positive" or "G:\TEAM\TEST\negative" depending of criteria in column C.

In other words, already created folders in the main directory to be there as long as they haven't set criteria. And when they have "yes" or "not" to be moved in subdirectories with all files in them (by running macro2).

I am sorry for my unclear explanations.

i'll be very grateful for the solving of this problem.

Have a nice day!

[Bob Phillips]

[VBA]Private Sub CommandButton1_Click()

Dim R As Range
Dim RootFolder As String

RootFolder = "G:\TEAM\TEST"
For Each R In Range("B4:B1411")

If Len(R.Text) > 0 Then

If R.Offset(0, 1).Value = "yes" Then

Name RootFolder & "\" & R.Value As RootFolder & "\positive\" & R.Value
Else

Name RootFolder & "\" & R.Value As RootFolder & "\negative\" & R.Value
End If
End If
Next R
End Sub[/VBA]

[kilikanzer]

I have Run-time error "53":
File not found

for this row:

Name RootFolder & "\" & R.Value As RootFolder & "\negative\" & R.Value

[Bob Phillips]

That suggests that one (or more) of the folders in your list does not exist. Wrap it in On Error.

[kilikanzer]

OK, it's working. But only for the first click on the macro button.

When i click for second time (on the first click folders are moved), run-time error 53 appears.

How can i avoid this and the macro to be looking only for new rows or new information in criteria column.

[Bob Phillips]

[VBA]Private Sub CommandButton1_Click()

Dim R As Range
Dim RootFolder As String

On Error Resume Next

RootFolder = "G:\TEAM\TEST"
For Each R In Range("B4:B1411")

If Len(R.Text) > 0 Then

If R.Offset(0, 1).Value = "yes" Then

Name RootFolder & "\" & R.Value As RootFolder & "\positive\" & R.Value
Else

Name RootFolder & "\" & R.Value As RootFolder & "\negative\" & R.Value
End If
End If
Next R
End Sub[/VBA]