Import only Visible Tabs within Workbook

[Miche25]

I have a folder named Import containing numerous excel files (all starting with OBR) and within those files I only want to import the visible tabs not any hidden tabs into an excel file called 'Ops Board Report'.
The Import folder where all the files I want to import into the Board Report are within
"C:\Users\MyName\Documents\Board Report\Board Report\Import"
the Board Report is within
"C:\Users\MyName\Documents\Board Report\Board Report\Ops Board Report.

How do I go about doing this?

 

[John_w]

Try adapting the code at http://www.mrexcel.com/forum/excel-...-putting-them-into-mult-tabs.html#post3606023. Some changes required would be to open the destination workbook and check for visible sheets (If ws.Visible = xlSheetVisible Then).

 

[Miche25]

Many thanks, for your help.

Sorry, I am new to VBA, In the code where would I put If ws.Visible = xlSheetVisible Then

Many thanks.

 

[John_w]

Immediately inside the For Next loop, like this.

Public Sub Copy_Visible_Sheets()

    Dim sourceFiles As String
    Dim destinationWorkbook As String, destWb As Workbook
    Dim sourcePath As String, fileName As String
    Dim ws As Worksheet
    
    sourceFiles = "C:\Users\MyName\Documents\Board Report\Board Report\Import\OBR*.xl*"
    destinationWorkbook = "C:\Users\MyName\Documents\Board Report\Board Report\Ops Board Report\Ops Board Report.xlsx"  'If needed, change xlsx to correct extension
    
    Application.ScreenUpdating = False
    
    Set destWb = Workbooks.Open(destinationWorkbook)
    
    sourcePath = Left(sourceFiles, InStrRev(sourceFiles, "\"))
    
    fileName = Dir(sourceFiles)
    Do While fileName <> ""
        Workbooks.Open fileName:=sourcePath & fileName, ReadOnly:=True
        For Each ws In ActiveWorkbook.Sheets
            If ws.Visible = xlSheetVisible Then
                ws.Copy after:=destWb.Sheets(destWb.Sheets.Count)
                destWb.Sheets(destWb.Sheets.Count).Name = fileName & " " & ws.Name
            End If
        Next
        Workbooks(fileName).Close
        fileName = Dir()
    Loop
    
    destWb.Close True
    
    Application.ScreenUpdating = True
    
End Sub

Put the code in a module of a macro-enabled workbook. I've assumed that your report workbook is a .xlsx file - change that line in the code if it is different.

 

[Miche25]

Thank you so much for your help John