Macro not pulling the correct data - Help!

[braidp]

Hi Guys,

I hope you can help with the below macro. It runs well as long as Sheet1 is visible (Ideally I'd like it to work even when sheet1 is hidden) however I've also now noticed that it's actually pulling the data from the last tabbed saved in the workbook rather than referencing Sheet1.

So if Sheet 1 is the tab saved before closing the workbook the macro works, If it is saved on a different tab then it pulls the wrong data.

Any help on this would be amazing.

Currently I use this to extract a line of data out of multiple workbooks to collate the information on one workbook

[COLOR=#333333]Sub LoopThroughDirectory()[/COLOR]
[COLOR=#333333]Dim Filepath As String[/COLOR]
[COLOR=#333333]Dim erow[/COLOR]
[COLOR=#333333]Filepath = Application.ActiveWorkbook.Path[/COLOR]
[COLOR=#333333]MyFile = Dir(Filepath + "\*.*")[/COLOR]


[COLOR=#333333]erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/COLOR]
[COLOR=#333333]Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents[/COLOR]


[COLOR=#333333]Do While Len(MyFile) > 0[/COLOR]
[COLOR=#333333]If MyFile = "ZMasterFile.xlsm" Then[/COLOR]
[COLOR=#333333]Exit Sub[/COLOR]
[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]Application.DisplayAlerts = False[/COLOR]


[COLOR=#333333]Workbooks.Open (Filepath & "" & MyFile)[/COLOR]
[COLOR=#333333]Range("A2:M900").Copy[/COLOR]


[COLOR=#333333]ActiveWorkbook.Close[/COLOR]




[COLOR=#333333]erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/COLOR]
[COLOR=#333333]ActiveSheet.Paste Destination:=Worksheets("Raw Data").Range(Cells(erow, 1), Cells(erow, 13))[/COLOR]


[COLOR=#333333]MyFile = Dir[/COLOR]


[COLOR=#333333]Application.DisplayAlerts = True[/COLOR]


[COLOR=#333333]Loop[/COLOR]


[COLOR=#333333]End Sub[/COLOR]

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1099750-running-vba-hidden-worksheets.html#post5287679

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).