VBA issue. runtime 91 - only occurring after change to Range?

[PTEOMARK]

Ok so I have a simple macro that is intended to open another workbook, copy the data from range ("E6:CD265") and paste it into the same range on original workbook. Code is below.
basically i want to open 'MASTER' Workbook, click Macro Button 'UpdateAll' and I need it to Open workbook 'RIV Forecast', worksheet 'RIV', select cells E6:CD265, return to MASTER workbook, worksheet 'RIV' paste data.
This happens for thirteen different sites, each with their own workbook to open and copy to a matching worksheet in the MASTER workbook, where i have a simple sum formula to add the data from each sites sheet to a single total.

This worked completely fine at the start of the week when the range was just E6:CD80, but since being instructed to include another 185 rows of items/data i now get a 'runtime error 91 - object variable or with block variable not set'

Again the only change in the code was the cell reference CD80 becoming CD265.

What have I broken?

Sub UpdateAll()

Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Set currentworkbook = ThisWorkbook
Set sourceworkbook = Workbooks.Open("https://companyname.sharepoint.com/:x:/r/sites/msteams_41568e/Shared%20Documents/River/RIV%20FORECAST.xlsx")

sourceworkbook.Worksheets("RIV").Range("E6:CD265").Copy
currentworkbook.Activate
currentworkbook.Worksheets("RIV").Activate
currentworkbook.Worksheets("RIV").Cells(6, 5).Select
ActiveSheet.Paste

sourceworkbook.Close
Set sourceworkbook = Nothing
Set currentworkbook = Nothing
ThisWorkbook.Activate
Worksheets("RIV").Activate
Worksheets("RIV").Range("E6").Select

 

[Micron]

It's not exactly clear which line (maybe you made this edit more than once) but I'm guessing it's this one:
sourceworkbook.Worksheets("RIV").Range("E6:CD265").Copy

If it's a failure due to the object variable then my guess is that sourceworkbook is empty because AFAIK, you cannot use url's in vba; you would have to use the UNC path to the file - //ServerNameHere/TopFolderHere/NextFolderHere/...

Characters like % are not part of the file path; they are part of the url.
You could put a break point on that line and use the immediate window to test the state of sourceworkbook.

EDIT - OK I forgot that this worked before your minor edit (I was called away after reading it), so I'm probably on the wrong path. Or you forgot about some other edit? Except you could still test the state of your variable.

 

[NdNoviceHlp]

What line of code errors? This looks odd...

Set currentworkbook = ThisWorkbook
Set currentworkbook = Nothing
ThisWorkbook.Activate

I think you could get rid of all that activating and selecting and just do this after the copy...

currentworkbook.Worksheets("RIV").Cells(6, 5).Paste

HTH.Dave

 

[PTEOMARK]

the error is on the line:

sourceworkbook.Worksheets("RIV").Range("E6:CD265").Copy

 

[Micron]

Since there is no With block in that section, it has to be due to the object not being Set before the line that raises the error. I still would verify that "sourceworkbook" actually contains an object:
msgbox IsEmpty(sourceworkbook)

 

[NdNoviceHlp]

Maybe trial replacing the whole copy paste thing with this...

Dim Rng As Range
Set Rng = sourceworkbook.Worksheets("RIV").Range("E6:CD265")
With currentworkbook.Worksheets("RIV")
.Cells(6, 5).Resize(Rng.Rows.Count, _
            Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End With

Dave

 

[PTEOMARK]

So I opened the workbook again today to trial Dave's suggestion - I spent hours last week trying to fix it.

and it worked without any changes! Thanks all for the help, will still test your code Dave