Macro: find/open/unprotect/unhide/copy/paste...

[JoshuaGA]

Hello, I need help with a Macro. I have found the individual elements but tying them all together where I'm unsure.

What I need to do is have a macro in (Workbook A) that will…

1. open a workbook based on a file path in a specific cell (Workbook B)
2. Unprotect Workbook B
3. Unhide a specific sheet on Workbook B
4. Copy the sheet and paste it into Workbook A
5. Then protect Workbook B and close it.

I found this code that does a good job of opening the specified Workbook, it’s tying the other steps into this that I’m not sure about.

Sub Test()
Dim strFName As String

strFName = ThisWorkbook.Worksheets("Mapping").Range("P9").Value
'this variable contains the workbook name and path
If FileExists(strFName) Then
'does it exist?
If Not BookOpen(Dir(strFName)) Then Workbooks.Open Filename:=strFName
'if its not already open, open it
Else
MsgBox "The file does not exist!"
End If


End Sub

Function FileExists(strfullname As String) As Boolean
FileExists = Dir(strfullname) <> ""
End Function

Function BookOpen(strWBName As String) As Boolean
Dim wbk As Workbook
On Error Resume Next
Set wbk = Workbooks(strWBName)
If Not wbk Is Nothing Then BookOpen = True
End Function

Thanks.