Running VBA on hidden worksheets

braidp

New Member
Joined
Dec 27, 2018
Messages
39
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
Code:
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
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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 ?
 
Upvote 0
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
 
Upvote 0
Perhaps something like this . . .

Code:
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.
 
Upvote 0
Tahnks again.................although I can't seem to get that to work :confused:
 
Last edited by a moderator:
Upvote 0
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
 
Upvote 0
Qualifying the copyFrom range with a reference to the correct sheet should solve your problem.
 
Upvote 0
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.
 
Upvote 0
Qualifying the copyFrom range with a reference to the correct sheet should solve your problem.
replace:
Code:
Range("A2:M900").Copy
with:
Code:
Sheets("TheNameOfYourSheet").Range("A2:M900").Copy
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top