0

in the following sub - that simply copies one named range to another on a different sheet - has always worked. now apparently something changed and it throws an unspecified runtime error

Sub prime_current_sim_input()
    
    With Range("varianten_in")
        .Value2 = Range("sim_direktinput_vals").Value2
    End With
End Sub

runtime error 1004

In Contrast, the following works fine

Sub prime_current_sim_input()
    Sheets("SIM").Select
    Range("$B$3:$GN$3").Select
    Selection.Copy
    Sheets("Varianten").Select
    Range("H3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

i'm at a complete loss as to what might cause this? ive already checked:

  • the first method has worked on my machine before and still works on others
  • All other subs, that work in a similar fashion on other named ranges also throw the same error
  • I have previous versions of the same file that work just fine
  • windows 10, excel 2016.
  • the named range dimensions/addresses haven't changed and are correct, they correspond.
  • all involved named ranges are defined on workbook level

Another Example of not working code, same error

Public Function Save_variant_inputs(offset As Integer) As Boolean
' copy  row to primer range "varianten_in"

With ThisWorkbook.Worksheets("Varianten").Range("varianten_in")
    .offset(offset).Value2 = .Value2
End With

Save_variant_inputs = True
End Function

Here is a view of the involved ranges: i dont see why this wouldnt work, can it be something with permission, sheet protection (is off on both)

overwatch screenshot of involved ranges

The error occurs in all lines of type

Range("a").Value2 = Range("b").Value2

it makes no difference how the subs and functions are called (both from vba directly and via a button yields the same result)

since this code broke without me touching it i suspect the cause is located somewhere else, not related to the code itself. it has worked before

i have added the sub to another workbook and there, it works fine.

simon_schaluppe
  • 1
  • 1
  • 1
    For **all** your ranges, specify in which worksheet your ranges are. Like: `ThisWorkbook.Worksheets("Sheet1").Range("varianten_in")` otherwise Excel does not know in which sheet to look for the range and it might sometimes work if it guesses right and sometimes not. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Mar 04 '22 at 11:06
  • That does not seem to be the issue. ive updated the quesiton – simon_schaluppe Mar 04 '22 at 11:16
  • Is the function `Save_variant_inputs` called by a formula in a cell or by VBA? What value did you use for `offset` and what address does the range `varianten_in` refer to? Can you add this information to the question too? • Which line exactly throws the error? – Pᴇʜ Mar 04 '22 at 11:22
  • both from a button on the sheet and directly running in VBA yields same error. – simon_schaluppe Mar 04 '22 at 11:30
  • the offset is not the issue, the error also occurs whithout an offset involved (first exampe) – simon_schaluppe Mar 04 '22 at 11:30
  • ive added an image on the namedrange.value2 overwatch, the datatypes and size matches. and it HAS worked before, the error must be related to something completely differnt – simon_schaluppe Mar 04 '22 at 11:32
  • Does it work if you set this up in an entirely fresh new workbook? I cannot get it to reproduce this error. – Pᴇʜ Mar 04 '22 at 11:36
  • yes, it works fine with new ranges, even in the same workbook – simon_schaluppe Mar 04 '22 at 13:02
  • That's strange, did you check the name manager if those names you use like `varianten_in` are unique? Maybe try to delete them in the name manager and re apply them. Looks like something odd went wrong here. – Pᴇʜ Mar 04 '22 at 13:09

0 Answers0