VBA - Paste Formats and Values

[CC268]

EDIT: Nevermind - I figured it out. Used xlPasteAllUsingSourceTheme.


Hello,

I'm trying to paste a sheet from one workbook to another (in the exact same formatting). The code works really well, but I'm having difficulty getting it to paste in the exact formatting.

I tried xlPasteAll (gets me close but doesn't have the borders), xlPasteValues (not formatting).

Option Explicit

Sub Insert_Process_List()


Dim wb As Workbook, CopyFromWbk As Workbook, CopyToWbk As Workbook
Dim ShToCopy As Worksheet, targetSheet As Worksheet, sht As Worksheet
Dim Answer As VbMsgBoxResult


Set CopyToWbk = ActiveWorkbook


Answer = MsgBox("            Open Program Processes List", vbOKCancel, "Open")
If Answer = vbCancel Then End
Set wb = FileDialog_Open()
If wb Is Nothing Then End
    
Call Sheet_Selector
    
Set CopyFromWbk = wb
Set ShToCopy = CopyFromWbk.ActiveSheet
ShToCopy.Cells.Copy
Set targetSheet = CopyToWbk.Sheets.Add(After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count))
targetSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
targetSheet.Name = "PROCESSES"
CopyFromWbk.Close False


End Sub

 

[DanteAmor]

Try this.
Copy the sheet from one book to the other.

Sub Insert_Process_List()
    Dim wb As Workbook, CopyFromWbk As Workbook, CopyToWbk As Workbook
    Dim ShToCopy As Worksheet, targetSheet As Worksheet, sht As Worksheet
    Dim Answer As VbMsgBoxResult
    
    Set CopyToWbk = ActiveWorkbook
    Answer = MsgBox("            Open Program Processes List", vbOKCancel, "Open")
    If Answer = vbCancel Then End
    
    Set wb = FileDialog_Open()
    If wb Is Nothing Then End
    Call Sheet_Selector
        
    Set CopyFromWbk = wb
    Set ShToCopy = CopyFromWbk.ActiveSheet
    ShToCopy.Copy after:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
    CopyToWbk.Sheets(CopyToWbk.Sheets.Count).Name = "PROCESSES"
    CopyFromWbk.Close False
End Sub