Copy-Paste different cells from each worksheet to another worksheet (excluding some)

Hello VBA Experts

I have a workbook which contains 50+ worksheets,

I want to copy Cells B1, K1 and L1 from each worksheet and paste it another worksheet ("OtherDatadump").

Also note that from this workbook few worksheets need to be excluded. These are with the names (Roles&Dropdowns, OtherDatadump, Summary, Consolidated Data, Project-SampleTemplate) in my workbook.

Can someone please help me?

I tried with below code, but this is not giving me desired results (For now I just tried to copy-paste cell K1).

Please Login or Register  to view this content.

The code which was supplied not producing the desired results. I am attaching my workbook maybe this helps in debugging and producing the expected results.

I am attaching screenshot with final results, how the final result should look like. I request to please modify the code (or write a new code accordingly). Thanks in advance!!
Screenshot-9.PNG

So why some worksheets are hidden, could the criteria be to include only visible worksheets after result worksheet ?

According to post #5 attachment - missing in initial post ! - a starter VBA demonstration
to paste only to Sheet8 (OtherDatadump) worksheet module :

PHP Code: 

Sub Demo1()
        UsedRange.Offset(1).Rows.Delete
        Application.ScreenUpdating = False
    For S& = Index + 1 To Worksheets.Count
    With Worksheets(S)
        If .Visible = -1 Then Rows(UsedRange.Rows.Count)(2).Columns("C:F") = Application.Index(.[A1:K1], 1, Array(2, 11, 5, 7))
    End With
    Next
        UsedRange.Borders.Weight = 2
        Range("E2:E" & UsedRange.Rows.Count).NumberFormat = "mmm-yyyy_W"
        Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Criteria or requirement for script is to exclude the worksheets which are with the names: Roles&Dropdowns, OtherDatadump, Summary, Consolidated Data, Project-SampleTemplate but include rest all others.

The reason for some sheets to be hidden is NOT to expose these to all users.

According to previous post a ThisWorkbook module VBA demonstration for starters :

PHP Code: 

Sub Demo2()
    Dim S$(), Ws As Worksheet
        S = Split("1 4 5 7 8")
   With Sheet8
       .UsedRange.Offset(1).Rows.Delete
    For Each Ws In Worksheets
     If IsError(Application.Match(Replace$(Ws.CodeName, "Sheet", ""), S, 0)) Then _
       .Rows(.UsedRange.Rows.Count)(2).Columns("C:F") = Application.Index(Ws.[A1:K1], 1, Array(2, 11, 5, 7))
    Next
       .UsedRange.Borders.Weight = 2
       .Range("E2:E" & .UsedRange.Rows.Count).NumberFormat = "mmm-yyyy_W"
   End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Hi TMS !

Logic error in your last VBA procedure, did you test it as it is ?

Ok so post #10 correction :

Please Login or Register  to view this content.

According to efficiency in case of big data each result row should be written at once rather than cell by cell
so the post #10 VBA procedure revamped :

PHP Code: 

Sub CopyPasteValuesFromAllWorkSheets2()
    Dim LastRow As Long, Ws As Worksheet
        LastRow = 1
    With Sheet8
       .UsedRange.Offset(1).ClearContents
        Application.ScreenUpdating = False
    For Each Ws In ThisWorkbook.Worksheets
    If Ws.Name <> "Roles&Dropdowns" And Ws.Name <> .Name And Ws.Name <> "Summary" And _
       Ws.Name <> "Consolidated Data" And Ws.Name <> "Project-SampleTemplate" Then
        LastRow = LastRow + 1
       .Rows(LastRow).Columns("C:F") = Array(Ws.[B1], Ws.[K1], Ws.[E1], Ws.[G1])
    End If
    Next
    End With
        Application.ScreenUpdating = True
End Sub 


Thank you Marc L and TMS very much and I really appreciate you time and efforts!!
I will be closing this loop.