Help to Combine VBA-code / run-time error 9 subscript out of range

[KlausW]

Hi

I use this VBA code to combine different sheets into one sheet, the name of this sheet is in cell D1.

The different sheets are dynamic in the number of rows from time to time.

In the sheet "Stamdata" from Cell A2 an down, there are the names of all the sheets to be collected.

In the "Stamdata” sheet, cell D2 contains the letters of the columns to be combined.

I get the following error message when I run the VBA code.

run-time error 9 subscript out of range

Some who can help.

Any help will be appreciated.

Best regards

Klaus W

Sub Rektangelafrundedehjørner2_Klik()

Dim wksHent As Range

Dim wksSaml As Worksheet

Dim sidsterk As Long

Dim kopiomr As Range

Dim kolonner As Variant

kolonner = Split(Names("HentKolonner").RefersToRange.Value2, ":")

Set wksSaml = Worksheets(Names("SamlearkNavn").RefersToRange.Value2)

wksSaml.Cells.ClearContents

For Each wksHent In Names("OpsamlingFra").RefersToRange.Cells

sidsterk = Worksheets(wksHent.Value2).Range("A" & Worksheets(wksHent.Value2).Rows.Count).End(xlUp).Row

'kopi dataomkr

Set kopiomr = Worksheets(wksHent.Value2).Range(kolonner(0) & "2:" & kolonner(1) & sidsterk)

wksSaml.Range("A" & wksSaml.Rows.Count).End(xlUp).Offset(1, 0).Resize(kopiomr.Rows.Count, kopiomr.Columns.Count).Value2 = kopiomr.Value2

'kopi overskrift

wksSaml.Range("A2").EntireRow.Value2 = Worksheets(wksHent.Value2).Range("A2").EntireRow.Value2

Next

End Sub

 

[rlv01]

You are referencing something that does not exist, likely a worksheet name that does not exist in the worksheets collection for that workbook. Your code loops though a name collection and builds worksheet names and defines range cell ranges based on that information . Usually it means that one of your worksheet names is not valid. Use the debugger to track down which one.

 

[KlausW]

Hi rlv01 thanks I will look. KW

 

[KlausW]

Hi rlv01, I've looked into it and can't find the error myself. Can you possibly help?
Thanks in advance Klaus W

File for help

 

[Alex Blakenburg]

Your code is cycling through the cells defined by the Named Range "OpsamlingFra".
This is the range A2:A4. A4 is blank so when it tries to use the value of A4 as a sheet name you get the subscript out of range error.