Running VBA on hidden worksheets

[braidp]

Hi All,

I currently run this macro on multiple documents in a folder. What I'd like to do is hide the worksheet - Sheet1 but still allow the macro to run.

Can anyone help with the below to allow the macro to extract the data from sheet1 whilst the worksheet remains hidden


Many thanks,


Paul

Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = Application.ActiveWorkbook.Path
MyFile = Dir(Filepath + "\*.*")


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents


Do While Len(MyFile) > 0
If MyFile = "ZMasterFile.xlsm" Then
Exit Sub
End If


Application.DisplayAlerts = False


Workbooks.Open (Filepath & "" & MyFile)
Range("A2:M900").Copy


ActiveWorkbook.Close




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


MyFile = Dir


Application.DisplayAlerts = True




Loop




End Sub

 

[Gerald Higgins]

I'm not getting into the detail of what your code actually does, but why not add some code to UNHIDE the sheet before the relevant part of the code, and then add another line to HIDE the sheet again once the main part has finished ?

 

[braidp]

I'm not getting into the detail of what your code actually does, but why not add some code to UNHIDE the sheet before the relevant part of the code, and then add another line to HIDE the sheet again once the main part has finished ?

That sounds great and exactly what I'm looking for - can you help with what code to use?

Many thanks

 

[Gerald Higgins]

Perhaps something like this . . .

Sheets("YourSheet").Visible = True

your main code goes here

Sheets("YourSheet").Visible = False

I'm not a code expert, there may be better ways of doing this.

 

[braidp]

Tahnks again.................although I can't seem to get that to work

 

[braidp]

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

 

[Yongle]

Qualifying the copyFrom range with a reference to the correct sheet should solve your problem.

 

[braidp]

Thank you Yongle - I am new to code, if you could help me I would really appreciate it by directing me to how I should type it?

Qualifying the copyFrom range with a reference to the correct sheet should solve your problem.

 

[Yongle]

Qualifying the copyFrom range with a reference to the correct sheet should solve your problem.

replace:

Range("A2:M900").Copy

with:

Sheets("TheNameOfYourSheet").Range("A2:M900").Copy

 

[braidp]

Yongle it worked like a dream - Thank you so much!!