0

I'm trying to Dim two different ranges and I really don't understand where I'm going wrong here, I have no trouble in "tdyRng" but when I want to set "ystRng" I get "Run-tim error 1004". Why is this not possible to set a Range in a previous sheet? The sheet is a copy of the activesheet, so all cells are the same. I would appreciate any help.

enter image description here enter image description here

newuser2967
  • 316
  • 1
  • 4
  • 15
Thomas Nørlund Rasmussen
  • 27
  • 3
  • 6
    You have the outer range object referencing an explicit sheet while the inner range object is referencing an implicit sheet. add ```ystSht.``` to the inner range. – Warcupine Feb 17 '20 at 14:38
  • 2
    Try using of `Set ystRng = ystSht.Range("C4", ystSht.Range("C4").End(xlDown))` instead of `Set ystRng = ystSht.Range("C4", Range("C4").End(xlDown))`. `Range("C4").End(xlDown)` reverences the active sheet... – FaneDuru Feb 17 '20 at 14:44
  • 2
    I wouldn't use `ActiveSheet` and `Previous`. What happens if the incorrect or first sheet is active when you run the code? Are your sheets named with the date - if so you could use something like `Format(Date,"dd-mmm-yy")` to return the sheet name for todays date. Also `tdySht`, `Cell`, and `tdyRange` are all `Variant` data types - you need to qualify each variable `Dim tdySht as Worksheet, ystSht as Worksheet`. – Darren Bartrup-Cook Feb 17 '20 at 14:52
  • Thank you all so much for your help! I guess i hit the wall yesterday and my head was full...I really appriciate the help, i know this have been covered before, but can be confusing looking at other exsamples where it all drowns in huge projects and lines of code. I know I still have a lot to learn, and activesheet and previous is one of the places where there is a lot room for improvement, code can onlye be run with i click on a commandbutton, so its not the worst scenario tho. I installed RubberDuck thanks to freeflow, i love it allready! – Thomas Nørlund Rasmussen Feb 18 '20 at 12:01

0 Answers0