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

 

[jolivanes]

Would you be able to just explain what you want to accomplish?

 

[Shiseiji]

Thanks for the response. I mentioned two worksheets, but was only working with one to get the code working. My apologies for the confusion if any over that. I'll address this for a single sheet.

After I copy the worksheet into the workbook it will always be sheets2. It will always have a different name so I can't use the worksheet name in the code and for learning and future use was I am trying to not use count.
So using the Sheets Index number I need the value from A1 (SumCell) copied onto Worksheet("Summary") tCell or Index number sheets1 Cells(2, 5).

Ron

 

[mikerickson]

What does the code do that you don't like?

 

[Shiseiji]

Greeting Mike, and Happy New Year. The code doesn't like my ignorance LOL.
"Object Variable or With Block Variable Not Set" error

Set SumCell = ThisWb.Range("OtherWs").Range(Cells(1, 1))

 

[Fluff]

Try

Set SumCell = OtherWs.Cells(1, 1)

 

[Shiseiji]

Sadly same error.

 

[Fluff]

There's a typo on this line

Set OtheWs = Worksheets(2)

you're missing the r.
If you put Option Explicit at the very top of each module(before any code) it will help you to spot tat sort of thing

 

[Shiseiji]

Thanks for the note on "Option Explicit." I've seen it and should have taken the time to learn what it does.

SumCell = OtherWs.Cells(1, 1)

Still errors.

 

[Fluff]

What error do you get?