Cannot get PasteSpecial to work but PasteSpecial data does?

[OaklandJim]

Oy, another basic issue I presume. In code below I paste values then paste formats. .Cells.PasteSpecial xlValues works but .Cells.PasteSpecial xlFormats does not. What am I doing wrong? I could use Paste but it seems that the target worksheet has to be active? FYI the worksheets are hidden if that matters. I don't believe that it does matter.

Sub CopyPasteToolings()

'   Workbook object for Target workbook
    Dim wbTarget As Workbook

'   Worksheet object for the Tooling worksheet in the Master workbook.
    Dim wsMasterTooling As Worksheet
   
'   Worksheet object for the just opened workbook.
    Dim wsTargetTooling As Worksheet
       
    Dim asFolders() As String
   
'   Count of folders to process.
    Dim iFoldersCount As Long
   
'   Used to loop through folders.
    Dim iFolder As Long
   
'   Count of files in folder.
    Dim iFilesCount As Long
   
'   Folder containing the source and target folders.
    Dim sBasePath As String
   
'   Full path plus filename
    Dim sFileSpec As String
   
'   Used to store the list of files after from the Dir function.
    Dim vFiles As Variant
   
'   Specify the number of folders to process.
    iFoldersCount = 3

'   Resize array to accommodate all folder names.
    ReDim asFolders(iFoldersCount) '<< set the Redim value to the number of folders to process
   
'   Load array with folder names. Ideally these are from values in a worksheet. That
'   way you do not have to access this code to change the folders to be accessed.
'
    asFolders(1) = "Other"
    asFolders(2) = "Plastic"
    asFolders(3) = "Steel"
   
    Application.ScreenUpdating = False
   
'   Master workbook has a worksheet named Tooling. Set the wsMasterTooling
'   worksheet object to point to that Tooling worksheet.
    Set wsMasterTooling = ThisWorkbook.Worksheets("Tooling")
   
'   Get the base path for all folders being processed.
    sBasePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1) & "\"

    For iFolder = 1 To iFoldersCount
       
        vFiles = Dir(sBasePath & asFolders(iFolder) & "\")
       
        While (vFiles <> "")

'           This is the full "file specification" with full path and filename.
            sFileSpec = sBasePath & asFolders(iFolder) & "\" & vFiles

'           Point the wbTarget object to the workbook that is opened.
            Set wbTarget = Workbooks.Open(sFileSpec)
           
            With wbTarget
           
                Set wsTargetTooling = wbTarget.Worksheets("Tooling")
           
                With wsTargetTooling
'                   Clear existing content in the Tooling worksheet in the target workboook.
                    .Cells.Clear

'                   Copy values from the Master workbook, Tooling worksheet.
                    wsMasterTooling.Cells.Copy

'                   Paste values and formats into the Tooling worksheet in target workbook (wbTarget).
                    .Cells.PasteSpecial xlValues
                    .Cells.PasteSpecial xlFormats
               
                End With
               
                .Close SaveChanges:=True
           
            End With
           
            vFiles = Dir
        Wend
           
    Next iFolder

    Application.CutCopyMode = False
   
    Call AddNames(wsTargetTooling)
   
End Sub

 

[OaklandJim]

I learned how to paste to a sheet without activating a worksheet by looking it up so this post is moot.

In code above I replaced the PasteSpecial with .Paste and got the desired result. '

.Paste Destination:=.Cells