0

What I would like to know is why some of the different ways in which I'm referencing a range are NOT working. To be clear, the code below is not complete, it's more of a stepping stone to what I'm looking to do ultimately, but baby steps before you can walk, right?

If anyone can help me understand why the last few lines of code do not work, I will appreciate it. As far as my understanding of ranges and cells goes, I thought they should. The subs are run from a UserForm after a command button is clicked.

 Option Explicit

 Dim arrCatalogNum() As String
 Dim strUserInput As String
 Dim strCatNum As String
 Dim rCatNum As Range
 Dim rRow As Range
 Dim ws1 As Worksheet
 Dim ws2 As Worksheet
 Dim currRow As Long
 Dim lastCol As Long

Sub GetCatalogArray()

     'Assign value to strUserInput
     strUserInput = UserForm1.Catalog_List.Value

     'Make array arrCatalogNum() from strUserInput
     arrCatalogNum = Split(strUserInput, ",")

     MsgBox "GetCatalogArray: strUserInput is " & strUserInput

     'Confirm bounds of the array
     MsgBox "LBound and UBound of arrCatalogNum is " & LBound(arrCatalogNum) & " and " & UBound(arrCatalogNum) & ", respectively."

End Sub

Sub TransferCatalogInformation()

     'Using ActiveWorkbook is tricky if you switch between different workbooks!
     Set ws1 = ThisWorkbook.Worksheets(1)
     Set ws2 = ThisWorkbook.Worksheets(2)

     'Get index of the last column with data in it
     lastCol = ws1.Cells.Find(What:="*", _
        After:=ws1.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious).Column

     MsgBox "TransferCatalogInformation: lastCol is " & lastCol

     'Counter for For statement
     Dim i As Integer

     With ws1
          For i = 0 To 0 'LBound(arrCatalogNum) To UBound(arrCatalogNum)

               'Set the cell range of rCatNum
               Set rCatNum = Range("A:A").Find(What:=arrCatalogNum(i))

               'Assign the variable currRow a value
               currRow = rCatNum.Row

               'Set the range of rRow as the entire row up to the last column  with data in it
               Set rRow = Range(rCatNum, Cells(currRow, lastCol))

               'Display what the cell address is
               MsgBox "TransferCatalogInformation: rCatNum cell address is: " & rCatNum.Address
               'Confirm what rRow range is
               MsgBox "rRow range is " & rRow.Address

               'Trying to copy from ws1 to ws2 of same workbook
               rRow.Copy Destination:=ws2.Cells(currRow, 1) 'WORKS
               rRow.Copy Destination:=ws2.Range("A1") 'WORKS
               rRow.Copy Destination:=ws2.Range(rCatNum, Cells(currRow, lastCol)) 'DOES NOT WORK. I believe I know why.
               rRow.Copy Destination:=ws2.Range(Cells(12, 1), Cells(12, 10)) 'DOES NOT WORK. I don't know why.
               rRow.Copy Destination:=ws2.Range(Cells(currRow, 1), Cells(currRow, lastCol)) 'DOES NOT WORK. I don't know why.
          Next i
     End With

End Sub
jcomp_03
  • 127
  • 1
  • 8

1 Answers1

0

You are using an implicit reference to the ActiveSheet property which may or may not be the worksheet that ws2 references. 

rRow.Copy Destination:=ws2.Range(rCatNum, Cells(currRow, lastCol))
'should be
rRow.Copy Destination:=ws2.Range(rCatNum, ws2.Cells(currRow, lastCol))