VBA Cell reference not working

[rplohocky]

Hello,
I wrote this code to look in a folder for a workbook and move it to another folder. The name of the folder and workbooks are based on cell values found in another workbook. I think I have everything right but the code can't find the first folder. Please look at the section that is referring to a cell location for the folder locations. I believe the issue is with the references "cellvalue3" and "cellValue2". The code right below are supposed to insert a slash after each reference but I don't think its working.

Sub Copy_One_File()

'Below sets a WORRKBOOK reference to ("6251 Vivint Rental Report.xlsm") workbook.
    Dim folderName As String
    Dim folderName2 As String
    Dim src As Workbook
    Set src = Workbooks("313670 Avanir Biweekly Dashboard Report.xlsm")
    
'Below set a CELL reference to the("313670 Avanir Biweekly Dashboard Report.xlsm") workbook.
'The numbers below refer to row number and column number respectivly. (33, 4) 33= row, 4 = column.
    Dim cellValue As String
    Dim cellValue2 As String
    Dim cellValue3 As String
    cellValue = src.Worksheets("Data").Cells(176, 1)
    cellValue2 = src.Worksheets("Data").Cells(168, 1)
    cellValue3 = src.Worksheets("Data").Cells(176, 2)
    
'Below is the original location of the workbook. This locatin is where it will be moved from.
    folderName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\Report Depository\" & cellValue
    
 '**************I THINK THIS IS THE TROUBLE AREA************************************   
'Below is where the workbook will be moved to.
    folderName2 = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\" & cellValue3 & cellValue2 & cellValue
'Below adds a backslash to the end of code above to locate the folder.
        If Right(cellValue3, 1) <> "\" Then
          cellValue3 = cellValue3 & "\"

    End If
            If Right(cellValue2, 1) <> "\" Then
          cellValue2 = cellValue2 & "\"

    End If
 '*************************************************************************************  
'Below says look at reference "folderName" and move workbook to reference "folderName2". This moves the workbooks to different folders.
    Name folderName As folderName2

    
    
End Sub

 

[Fluff]

Check for & add the \ before you assign a value to folderName2

 

[rplohocky]

Check for & add the \ before you assign a value to folderName2

I'm not sure what you mean here. The slash needs to after the value so it refers to a folder.

'Below is where the workbook will be moved to.
    folderName2 = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\" & cellValue3 & cellValue2 & cellValue
'Below adds a backslash to the end of code above to locate the folder.
        If Right(cellValue3, 1) <> "\" Then
          cellValue3 = cellValue3 & "\"

    End If
            If Right(cellValue2, 1) <> "\" Then
          cellValue2 = cellValue2 & "\"

    End If

The fianl address should look like this.

\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\Violations\July-22-2018\Violations Report.xlsx

 

[Fluff]

At the moment you create a folder path (folderName2), then you change the values that make-up the path (cellvalue3 & 2).
Those changes are not going to be reflect in your path, so you need to change the cell values before creating the path

 

[rplohocky]

At the moment you create a folder path (folderName2), then you change the values that make-up the path (cellvalue3 & 2).
Those changes are not going to be reflect in your path, so you need to change the cell values before creating the path

I wasn't quite sure where to make my changes so I fixed it another way. I added a slash to the end of folder name inside the referenced cell and it worked as expected.

The only other thing that would be nice to add to this would be a error message if the macro doesn't find a workbook in the folder to move? Any ideas on that?

BTW thanks for the help!

 

[Fluff]

How about

   If Len(Dir(FolderName)) = 0 Then
      MsgBox "no file"
      Exit Sub
   End If

 

[rplohocky]

How about

   If Len(Dir(FolderName)) = 0 Then
      MsgBox "no file"
      Exit Sub
   End If

Hello Fluff,
With regard to your code above, I'm not getting a pop up message but instead a runtime error. Based on the code I inserted below would this pop up warning work if the final folder destination was NOT there?

Sub Copy_One_File()
'This macro looks in the Depository folder and moves each of the WB's into the workbookfor which they are named.

'Below sets a WORRKBOOK reference to ("6251 Vivint Rental Report.xlsm") workbook.
    Dim StartfolderName1 As String

'Below set a CELL reference to the("313670 Avanir Biweekly Dashboard Report.xlsm") workbook.
'The numbers below refer to row number and column number respectivly. (33, 4) 33= row, 4 = column.
    Dim cellValue1A As String
    Dim cellValueFolderName As String
    Dim cellValue1C As String

cellValue1A = src.Worksheets("Data").Cells(176, 1) 'WB name.
    cellValueFolderName = src.Worksheets("Data").Cells(168, 2) 'New folder name.
    cellValue1C = src.Worksheets("Data").Cells(176, 3) ' New folder name with slash on the end.

'Below is the original location of the workbook. This locatin is where it will be moved from.
    StartfolderName1 = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\Report Depository\" & cellValue1A

'Below is where the workbook will be moved to.
    FinalfolderName1 = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\313670 Avanir\" & cellValue1C & cellValueFolderName & cellValue1A

'Below says look at references above to move the workbooks from the Depository Folder to there respective folders.
    Name StartfolderName1 As FinalfolderName1
       If Len(Dir(FinalfolderName1)) = 0 Then
      MsgBox "no file"
      Exit Sub
   End If

End Sub

 

[Fluff]

That code was to check if the file existed before trying to move it, which is what you asked for.
But you have changed it to look at the renamed file & placed it after you have tried to rename the file, hence you still get errors.
What exactly are you trying to do?