VBA Issue copying sheet from one external workbook to another

[PBN123]

Hi
This one has been driving me crazy for several hours yesterday and grateful for any help received.

Background
I have a workbook (ThisWorkbook) that iterates through a folder and collates data from several workbooks (srcWBKs) and copies it to a target output workbook (destWBK). The srcWBKs are protected from users so unless the user clicks on an front sheet with an accept disclaimer all the sheets are very hidden and the VBA / project properties is protected with a password. The workbook and the worksheets in srcWBKS are not protected. I have successfully written the rest of the code that copies data from named ranges on the srcWBKs to the destWBK. However, on one sheet there are photos and logos. The solution I adopted was to simply copy this sheet from srcWBK to destWBK and hardcode to cut links and formulas.

Issue
For some reason the code runs happily but the copy action never appears to happen. It simply fails to execute and then keeps running. However, no worksheet is copied and no error is raised (which would be preferable as then it would give me more of a clue as to the issue).

Things I have tried
- unhiding / activating / selecting the sheet on srcWBK before copying
- I have tried copying the sheet to the end of the srcWBK, hardcoding it and then moving it to destWBK - partially works as able to copy and hardcode, but then the code seems to lose focus and does not complete the move to destWBK
I have been able to access both sheets by during break mode checking if I could see values from each sheet through the Immediate window
- I have tried ?workbooks.count which shows the correct number of workbooks so it does not appear to be a new instance of Excel issue

Potential solutions
I suspect that the issue could be that the srcWBK has the VBA protected (none of the sheets or workbook is protected) - therefore it does not appear in the VBE. Is there a way to unprotect and then re-protect? - not an ideal solution as don't want passwords stored - however if it works a step forwards!

Here is the skeleton of the code:

Sub ImportFromFile()

dim srcwbk as workbook, destWBK as workbook
dim strPropName as string, strPropRef as string

Set srcwbk = Workbooks.Open(Myfile.Path) ' Myfile.path derived from file picker
Set destWBK = Workbooks.Add

' also pass strings with the name and reference of the property 
Call CopyReportFrontPage(srcwbk, strPropRef, strPropName, destWBK)

Exit Sub

Function CopyReportFrontPage(srcwbk As Workbook, strPropRef As String, _
                    strPropName As String, destWBK As Workbook)
'Code here that determines the name of the sheet to be copied from a codename and sets it to srcWksName
With srcwbk 
.Worksheets(srcWksName).Visible = xlSheetVisible
.Worksheets(srcWksName).Activate
.Worksheets(.Worksheets.Count).Copy After:=destWBK.Worksheets(destWBK.Worksheets.Count) 'It passes over this without doing anything or erroring 
End With

 With destWBK
'This should hardcode and rename the new sheet - however with no new sheet there is only affects the original sheet
 .Worksheets(.Worksheets.Count).UsedRange.Value = .Worksheets(.Worksheets.Count).UsedRange.Value
   .Worksheets(.Worksheets.Count).Name = strExt
end with
End function

Welcome any insight / new things to try

Thanks

Paul

 

[Norie]

Paul

Have you tried stepping through the code line by line using F8?

That might give some insight on what's happening.

P.S. You say that's the 'skeleton' of the code? Is there any other code, perhaps something that's suppressing errors/warnings?

 

[PBN123]

Paul

Have you tried stepping through the code line by line using F8?

That might give some insight on what's happening.

P.S. You say that's the 'skeleton' of the code? Is there any other code, perhaps something that's suppressing errors/warnings?

Norie - thanks for coming back to me on this.
I have stepped through the code to try to debug and when running the function - in the immediate window both:
?.Worksheets(srcWksName).range("c2").value
?destWBK.Worksheets(1).range("B4").value
will successfully return the correct values from the sheets - so the sheets are "visible" to the code.
In terms of error handling I have the usual:

' Code to set up environment
'Application.ScreenUpdating = False
'Application.EnableEvents = False
'Application.Calculation = xlCalculationManual
'Application.DisplayAlerts = False
'Application.AskToUpdateLinks = False ' this prevents the model pausing when there are external links present


' This setting below restricts macro security - so that if there is a workbook open macro it will not run it
'Dim varCurrentMacroSecuritySetting As Variant
'varCurrentMacroSecuritySetting = Application.AutomationSecurity
'Application.AutomationSecurity = msoAutomationSecurityForceDisable

But for debugging these are commented out.
Welcome your further thoughts
Thanks
Paul

 

[MrBillBenson]

Really late to the party here but I ran into this question - seemingly unanswered ?? Or am I wrong. Anyway what I have seen is that when automationsecurity is set to msoAutomationSecurityForceDisable, the same call stack that opened the external workbook cannot copy or move sheets into or out of it, and the statement that tries to do so simply fails. I have had to break up my routines so I open in one call stack and use Application.OnTime Now(),"StepTwo" before that call stack is finished, and be sure to set a public variable to the workbook (or even the sheet if that is what I am after); then the StepTwo process takes over. In StepTwo I set AutomationSecurity = msoAutomationSecurityLow, do the copystep, and then set AutomationSecurity back to msoAutomationSecurityForceDisable (if I have more workbooks to open). Leaving it set to 1 is not a problem most of the time, I think allowing macros to run within a workbook is a Y/N forever thing spelled out at the time the external workbook is opened. Setting AS to msoAutomationSecurityLow doesn't make any code in that workbook runnable.

 

[PBN123]

MrBillBenson - thanks for coming back to this one. Whilst it has been a long time this issue might crop up again for both me and others. Your idea gives me a new path to explore - and often whilst most solutions are unique part of the issue is simply discovering places where the errors might be located​

I am going to be working on something similar to this over the next couple of months so really appreciate your time and input to this​

PBN123​