VBA-Compile Error: 'Object required' when setting a value

Rymare

New Member
Joined
Apr 20, 2018
Messages
37
I have a list of folder paths in column A (which will change every time I run other macros that take user input) and one Folder path in column b row 1 (which will also change depending on other macros that take user input)


| COLUMN A | COLUMN B
----------------------------------------------------
1 | C:\test\AAA\1 | C:\NewPackage\BBB |
----------------------------------------------------
2 | C:\test\AAA\2 |
----------------------------------------------------
3 | C:\test\AAA\3 |
----------------------------------------------------
4 | C:\test\AAA\4 |
----------------------------------------------------


I want to copy all the folders (and their contents etc) to the folder in column B.

I have the code below and it SHOULD work, for some reason though I keep getting an error message:

Compile Error: Object Required

at the line
Code:
sTarget =

Which is weird because I feel like logically it should work, I literally JUST set sTarget as a string in the code right before.

Can anyone help? It's a broad error and I'm finding a lot of things in google, but none of them seem to be working for me.

The code is adapted from a stackoverflow answer I found--I also added some notes

Code:
Sub MoveModules()

  'Column A should have a list of paths like
    'C:\test\AAA\1
    'C:\test\AAA\2
    'C:\test\AAA\3
    'C:\test\AAA\4
    'etc. the list changes length
  'Column B, row 1 (aka Sheets("Output3").Range("B1").Value) should have a path like C:\NewPackage\BBB


  'This move would COPY C:\test\AAA\1 (etc) to C:\NewPackage\BBB


  Dim wso3 As Worksheet
  Dim rng As Range
  Dim aData As Variant
  Dim FSO As Scripting.FileSystemObject
  Dim iCounter As Long
  Dim sOrigin As String
  Dim sTarget As String: Set sTarget = Sheets("Output3").Range("B1").Value


  'Get the intersection of columns A and B and the usedrange
  Set rng = Range("A:A" & Sheet2.UsedRange.Address)


  If Not rng Is Nothing Then


    aData = rng
    Set FSO = New FileSystemObject


    For iCounter = LBound(aData) To UBound(aData)
      sOrigin = aData(iCounter, 1)
      If FSO.FolderExists(sOrigin) Then
        If Not FSO.FolderExists(sOrigin) Then
          FSO.CopyFolder sOrigin, sTarget
        Else
          MsgBox "Target folder already exists at '" & sTarget & "'"
        End If
      Else
        MsgBox "Can't find Source folder '" & sOrigin & "'"
      End If
    Next iCounter
  End If
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You only use Set with objects.
 
Upvote 0
You do not use the word "Set" when setting strings, values, dates, booleans, etc.
You use that when assigning objects, like ranges, worksheets, workbooks.
Code:
Dim sTarget As String: sTarget = Sheets("Output3").Range("B1").Value
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top