×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Ignore Hidden Sheet & AutoFill Fail Safe

Ignore Hidden Sheet & AutoFill Fail Safe

Ignore Hidden Sheet & AutoFill Fail Safe

(OP)
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?

CODE -->

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.

RE: Ignore Hidden Sheet & AutoFill Fail Safe

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close