Little Tweek needed in VBA Code

[GaryG9595]

Hello,
I have a code (Below) that works ok. This code goes into the file that I name on my "CG" sheet from A1.
Pulls the data I need perfectly, except if the file has two sheets on it, it pulls from the sheet that was open during it's last save.

For Example, the workbook has two sheets, one named "CG" and the other "4-13 report". If they made changes to the "4-13 Report", and hit save, when I run this code, I get the "4-13 Report" data and not the "CG" I need.

Currently I am going into each Workbook, selecting the "CG" sheet, clearing the data, unhiding the data, and then clicking save. Once I do that, this Code works great.
I would like this code to go into the workbook and always pull from the "CG" Sheet. Also Unhide and clear the data before the pull just in case they have some filtered or hidden.

Does anyone know how to add to this code to make the above red sentence happen?

Thank you in advance,
Gary

Sub simpleXlsMerger1()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

Set dirObj = mergeObj.Getfolder(Sheets("CG").Range("A1").Value)
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A8:AC" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

 

[Fluff]

What do you mean by "clearing the data"?

 

[GaryG9595]

What do you mean by "clearing the data"?

Hi Fluff,
If they have anything filtered, I go in and clear the filter and sort state for the current range of data. On the DATA Tab next to the filter button, I just hit clear.
Hope that helps.
Thansk,
Gary

 

[Fluff]

Ok, how about

Sub simpleXlsMerger1()
   Dim bookList As Workbook
   Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
   Application.ScreenUpdating = False
   Set mergeObj = CreateObject("Scripting.FileSystemObject")
   
   Set dirObj = mergeObj.Getfolder(Sheets("CG").Range("A1").Value)
   Set filesObj = dirObj.Files
   For Each everyObj In filesObj
      Set bookList = Workbooks.Open(everyObj)
      With bookList.Sheets("CG")
         .AutoFilterMode = False
         .Range("A8:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
         ThisWorkbook.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
      End With
      Application.CutCopyMode = False
      bookList.Close
   Next
End Sub

 

[GaryG9595]

Ok, how about

Sub simpleXlsMerger1()
   Dim bookList As Workbook
   Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
   Application.ScreenUpdating = False
   Set mergeObj = CreateObject("Scripting.FileSystemObject")
  
   Set dirObj = mergeObj.Getfolder(Sheets("CG").Range("A1").Value)
   Set filesObj = dirObj.Files
   For Each everyObj In filesObj
      Set bookList = Workbooks.Open(everyObj)
      With bookList.Sheets("CG")
         .AutoFilterMode = False
         .Range("A8:AC" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
         ThisWorkbook.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
      End With
      Application.CutCopyMode = False
      bookList.Close
   Next
End Sub

That worked. Thanks you so much. This is also helping me understand coding as well. Thank you again for your help.

 

[Fluff]

You're welcome & thanks for the feedback.