VBA Paste Special Values & Formats Between Workbooks (No Selecting or Activating)

[Accounting BI]

Hi all, I have a macro that is doing exactly what I want, however, I've become aware that Activating and Selecting is bad practice, and I want to do this right. I believe this section is also causing screen flicker and a 'disappearing' userform when calling this from my userform.

I'm essentially just copying some data from target workbooks (wbOpen), and looping through each sheet of each workbook and creating a consolidated version in wbConsol. It's columns being appended, so I need to find the first available column in each sheet to tell the macro where to paste next. This all works fine, but the only way I can achieve the Paste is by Activating the target workbook and Selecting the target range. I think what's complicating it for me is needing to Paste Special, and also the fact I've currently got two steps to arrive at my target range.

I should note that the target workbooks are not identical in structure, so the next unused column range could be different on each sheet as the file builds.

This is the code snippet - apologies, I'm not a programmer! Any help is appreciated.

With wbOpen

        wsCount = wbConsol.Worksheets.Count
        SourceNameFormat = StrConv(SourceName, vbProperCase)

        For i = 1 To wsCount
        Department = wbConsol.Worksheets(i).Name
                
                If sheetExists(wbOpen.Department) Then
                    .Worksheets(Department).Range("P5").Value = SourceNameFormat
                    .Worksheets(Department).Range("P5").WrapText = True
                    .Worksheets(Department).Range("P1:P200").Copy
                    
[B]                     wbConsol.Worksheets(Department).Activate
                     wbConsol.Worksheets(Department).Cells(1, Columns.Count).End(xlToLeft).Select
                     ActiveCell.Offset(0, 1).Select
                     ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
                     ActiveCell.PasteSpecial xlPasteFormats
                     Application.CutCopyMode = False[/B]
                End If
        
        Next i
    
.Close SaveChanges:=False
                
End With

 

[Accounting BI]

Sorry just as an update - through a further bit of experimentation, I've stored the target cell syntax as parameters and passed the result to the paste function. It seems to work fine, but can anyone see any issues with this?

Revised code below.

Thanks!

With wbOpen

        wsCount = wbConsol.Worksheets.Count
        SourceNameFormat = StrConv(SourceName, vbProperCase)

        For i = 1 To wsCount
        ' On error used to skip departments not present in the target file that may be present in the new file (e.g. Fleet)
        On Error Resume Next
        Department = wbConsol.Worksheets(i).Name
        Set lastUsed = wbConsol.Worksheets(Department).Cells(1, Columns.Count).End(xlToLeft)
        Set firstBlank = lastUsed.Offset(0, 1)
        
                
                If sheetExists(wbOpen.Department) Then
                    .Worksheets(Department).Range("P5").Value = SourceNameFormat
                    .Worksheets(Department).Range("P5").WrapText = True
                    .Worksheets(Department).Range("P1:P200").Copy
                     firstBlank.PasteSpecial xlPasteValuesAndNumberFormats
                     firstBlank.PasteSpecial xlPasteFormats
                     Application.CutCopyMode = False
                End If
        
        Next i
    
.Close SaveChanges:=False
                
End With

End If