Ranges "Set" = I'm Lost

[Shiseiji]

Daily I copy two worksheets into a new workbook. The worksheets names change daily so I'm trying to reference the sheets by index number and get the value from Cells(1 , 1). But I'm not getting the ranges set properly for the reference/copy from cells and can't figure it out. My initial attempt was to use "With" but I couldn't get that written properly either.

TIA

Sub tm_PastUser()'
   Sheet1.Activate
   '
   Dim tCell    As Range
   Dim SumCell  As Range
   '
   Dim ThisWb   As Workbook
   '
   Dim ThisWs   As Worksheet
   Dim OtherWs  As Worksheet
   '
   Set ThisWs = Worksheets(1)
   Set OtheWs = Worksheets(2)
   Set tCell = ThisWs.Cells(2, 5)
   'Set SumCell = ThisWb.OtherWs.Cells(1, 1)
   Set SumCell = ThisWb.Range("OtherWs").Range(Cells(1, 1))
   '
   SumCell.Copy tCell
  Application.CutCopyMode = False
End Sub

As always, I appreciate the help.

Ron

 

[Shiseiji]

Sorry, this was buried earlier on: "Object Variable or With Block Variable Not Set" error. When I hover over the code, I'm shown in a field the proper information. Sorry I don't know what that's called and I don't seem to have the right type of place to park a picture so I can upload it . . .

 

[Fluff]

This works for me

Sub tm_PastUser() '
   'Sheet1.Activate
   '
   Dim tCell    As Range
   Dim SumCell  As Range
   '
   Dim ThisWb   As Workbook
   '
   Dim ThisWs   As Worksheet
   Dim OtherWs  As Worksheet
   '
   Set ThisWs = Worksheets(1)
   Set OtherWs = Worksheets(2)
   Set tCell = ThisWs.Cells(2, 5)
   Set SumCell = OtherWs.Cells(1, 1)
   '
   SumCell.Copy tCell
  Application.CutCopyMode = False
End Sub

 

[Shiseiji]

Thanks! And I figured out my dumb error. Then the power went out! I created a new workbook, saved as a .xlsm, and pasted in your code and two worksheets. Was surprised to see when I copied in the two worksheets that they came in as Sheet4 & sheet5. Duh, Blinding Flash of the Obvious . . . I'd confused Index Number Sheet Number

Sheet1 is the sheet number
Worksheets(1) is the Worksheet Index Number
Worksheets("Sheet1") "Sheet1" is the sheet Name


Half way through my troubleshooting I'd changed from sheet number to index number.
NB for others reading this later: Index number = worksheet sequence. That changes anytime a worksheet is moved in a workbook, and it can only be changed by changing a worksheet's order in the workbook). A quick test shows that when worksheets are copied into a workbook they either retrain their sheet number, sheet5, even if there isn't a sheet4, but will be given the next sheet number if there is a conflict.

When the code was corrected it was rebroken due to my trying to use a nonexistent sheet Index number.

 

[Fluff]

Glad it's sorted & thanks for the feedback.

For reference Sheet1 is not the sheet number, it's the sheet codename.