Ignore Hidden Sheet & AutoFill Fail Safe

[Nick V]

Hi all,

I have managed to make a loop which looks through all tabs in all files (both tabs and files are unspecified). The code copies a set of data and pastes into a Data Dump file.
There is a problem when there is only ONE entry in any tab due to the AutoFill section of the macro. Would anyone be able to help with adding a fail safe for this so it doesn't error even with one entry and carries on?

The other problem is that there are hidden sheets within the files I am copying data from. Can these hidden tabs be ignored so it isn't picked up?

Sub Execute_Files()
Dim objFSO As Object, objFolder As Object, objFile As Object
Dim Path As String
Dim ThisWorkbook As String
Dim sht As Integer


' VBA to access and extract data from SharePoint to file within SharePoint.
' This looks at every file in the SharePoint site.

ThisWorkbook = "DataDump_v2.xlsb"
Application.AskToUpdateLinks = False
RowNumber = 2

' Define paths to folders that contain files to execute

Path = "C:\Users\040428\Desktop\LiamG\Excel_development_work_for_Brick_by_Brick_\Timesheets\"
Application.DisplayAlerts = False
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Path)

For Each objFile In objFolder.Files
Workbooks.Open Filename:=Path & objFile.Name


'here need to add something to + 1 sheet

Workbooks(objFile.Name).Activate
Sheets(1).Activate

'The start of the bulk code work

For sht = 1 To Workbooks(objFile.Name).Worksheets.Count


Workbooks(objFile.Name).Activate
Range("C17:G33").Copy

Windows("DataDump_v2.xlsb").Activate
Sheets("RawData").Select
        Range("C" & RowNumber).PasteSpecial Paste:=xlPasteValues
    
   
Workbooks(objFile.Name).Activate
Range("D3:G3").UnMerge
Range("D3").Copy

Windows("DataDump_v2.xlsb").Activate
Range("A" & RowNumber).PasteSpecial Paste:=xlPasteValues
Range("A" & RowNumber).AutoFill Destination:=Range("A" & RowNumber & ":A" & Range("D" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

RowNumber = RowNumber + 19

Application.CutCopyMode = False

Workbooks(objFile.Name).Activate
ActiveSheet.Next.Activate

Next sht

Windows("DataDump_v2.xlsb").Activate

Workbooks(objFile.Name).Close savechanges:=False

Next
Application.DisplayAlerts = True
End Sub

Thank you.

 

[DjangMan]

I would want to you do the following first:

Remove all of the .Activate code - reference the ranges directly.
Create two workbook references to allow you to reference two workbooks at the same time and avoid the Windows.Activate calls.

That will give you a huge speed boost.

Use a variable to store the value of Workbooks(objFile.Name).Worksheets.Count because each loop will require that value to re-calculated. Now, for the number of sheets in a workbook - that's very small calculation. But if you're looking at going from 1 to the last used row in a workbook - that can be a much more significant delay.

So, along those lines, if you are storing the number of rows in line of code used in your .AutoFill you can set up logic to avoid calling autofill if there is a single row. Hopefully that gives you the answer that you're needing.