VBA: All Sheets data to Master Sheet (excluding some sheet)

[Samantha27]

Hi Good People,

Okay so I'm having a hard time fixing this code that I came across from my research.. What I wanted to achieve is this..

I have a

MasterSheet - Sheet1-Sheet2-Sheet3-NotesSheet, NoteSheet 2.

I have a macro button in the masterfile sheet that will extract the data from Sheet1-Sheet3 but exclude data from notesheet and notesheet 2.

The masterfile have the same heading of sheet1-sheet3 so I just need to extract the data daily. Up to the next available cell, but not over ride the previous data.

below is my code..

Please help me achieve this.. I can't stress this enough.. Below is my code. Thank you!!

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "c:\Users\michal\Documents\Macro\"
    ChDir strPath
    strExtension = Dir("*.csv*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets(1).Range("A1:E" & LastRow).Copy wkbDest.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[kevin9999]

OK, try the following instead:

Option Explicit
Sub Copy_Some_Sheets_V3()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, wsM As Worksheet
    Set wsM = Worksheets("Master")
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Master" And ws.Name Like "Note*" = False Then
            If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
                With ws.Range("A1").CurrentRegion
                    .AutoFilter 4, "<>Done"
                    .Offset(1).Resize(.Rows.Count - 1, 3).Copy wsM.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                    .AutoFilter
                End With
            ws.Range("D2:D" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Value = "Done"
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[kevin9999]

Edited

Option Explicit
Sub Copy_Some_Sheets_V4()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, wsM As Worksheet
    Set wsM = Worksheets("Master")
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Master" And ws.Name Like "Note*" = False Then
            If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
                With ws.Range("A1").CurrentRegion
                    .AutoFilter 4, "<>Done"
                    If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
                        .Offset(1).Resize(.Rows.Count - 1, 3).Copy wsM.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                    End If
                    .AutoFilter
                End With
            ws.Range("D2:D" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Value = "Done"
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub