transfers the data, but not from all sheets.

[KlausW]

Hi all Excel helper
I use the VBA code I got from a good helper here on MrExcel. It transfers the data from all the sheets, to the sheet "Bestilling" . It works really well.
Now I would like to insert a sheet before or after the sheet "Bestilling" that the VBA-code, does not transfer the data from.
All help will be appreciated
Best regards Klaus W

Sub Rektangelafrundedehjørner4_Klik()

    Dim ws As Worksheet, wsConsol As Worksheet
    Dim lngLastRow As Long, lngPasteRow As Long
   
    Application.ScreenUpdating = False
   
    Set wsConsol = ThisWorkbook.Sheets("Bestilling")
    lngPasteRow = 9 'Starting Row number for the consolidation of the sheet data in 'wsConsol'.
   
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsConsol.Name Then
            On Error Resume Next
                lngLastRow = ws.Range("A:I").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            On Error GoTo 0
            If lngLastRow >= 2 Then
                ws.Range("A2:I" & lngLastRow).Copy Destination:=wsConsol.Range("K" & lngPasteRow)
                lngPasteRow = wsConsol.Range("K:S").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            End If
        End If
    Next ws
          
            Range("K9:K15000").NumberFormat = "General"
            Range("K9:K15000").Value = Range("K9:K15000").Value
      
    Application.ScreenUpdating = True
    
    Sheets("Bestilling").Select
    Macro1

End Sub

 

[Fluff]

You can add the sheet name like

If ws.Name <> wsConsol.Name And ws.Name <> "Sheet1" Then

 

[KlausW]

You can add the sheet name like

If ws.Name <> wsConsol.Name And ws.Name <> "Sheet1" Then

Hi Fluff,
I dont understand that. Where should I put it in the vba code. KW

 

[Fluff]

In place of this line

If ws.Name <> wsConsol.Name Then

 

[KlausW]

You can add the sheet name like

If ws.Name <> wsConsol.Name And ws.Name <> "Sheet1" Then

In place of this line

If ws.Name <> wsConsol.Name Then

Unfortunately, when I insert that line, it does not happen. KW

 

[Fluff]

Did you change the "Sheet1" to match the name of the sheet?

 

[KlausW]

Did you change the "Sheet1" to match the name of the sheet?

Ha ha, I had entered the wrong sheet name. It's solved, thank you very much. KW

 

[Fluff]

You're welcome & thanks for the feedback.