Copy data from one excel workbook to another while retaining formatting

Question

I am new to excel VBA. I have already written VBA code to select any Excel file and copy path of that file to cell A1. Using the path I am trying to copy contents of source file, Sheet7, while retaining cell formatting i.e. bold, borders, colors, etc.

My first error is appearing for file path. Currently cell A1 value = C:\Users\Personal\Documents\Excel files\Dummy-Data - Copy.xlsx. When I try to read value of A1 cell, VBA throws me an error "Sorry, we couldn't find. Is it possible it was moved, renamed or deleted?" and automatically clears the value of cell A1. But when I give the same path directly in VBA script, it works! Can someone tell me how to get this fixed?

My second doubt is around copying cell formats. When I use wksht.paste to paste copied content to Sheet2, it just pastes all cell values without formatting. But when I try to use PasteSpecial following error occurs- "Application-defined or object-defined error" . Can someone help me correct this please?

    Sub Button1_Click()
        ' define variables
        Dim lastRow As Long
        Dim myApp As Excel.Application
        Dim wbk As Workbook
        Dim wkSht As Object
        Dim filePath As Variant

    'on error statement
    On Error GoTo errHandler:

        ' Select file path
        Set myApp = CreateObject("Excel.application")
        Sheet2.Range("A1").Value = filePath
        Set wbk = myApp.Workbooks.Open(filePath)
        'Set wbk = myApp.Workbooks.Open("C:\Users\Personal\Documents\Excel files\Dummy-Data - Copy.xlsx")

    ' Copy contents
        Application.ScreenUpdating = False
        lastRow = wbk.Sheets(7).Range("A" & Rows.Count).End(xlUp).Row
        wbk.Sheets(7).Range("A2:Q" & lastRow).Copy
        myApp.DisplayAlerts = False
        wbk.Close
        myApp.Quit

    ' Paste contents
        Set wbk = Nothing
        Set myApp = Nothing
        Set wbk = ActiveWorkbook
        Set wkSht = wbk.Sheets("Sheet2")
        wkSht.Activate
        Range("A2").Select
        wkSht.Paste
        'wkSht.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False

        Application.ScreenUpdating = True
        Exit Sub

    'error block
errHandler:
            MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
            & Err.Number & vbCrLf & Err.Description & vbCrLf & _
            "Please follow instruction sheet"

        End Sub

Answer 1

_{My first error is appearing for file path. Currently cell A1 value = C:\Users\Personal\Documents\Excel files\Dummy-Data - Copy.xlsx. When I try to read value of A1 cell, VBA throws me an error "Sorry, we couldn't find. Is it possible it was moved, renamed or deleted?" and automatically clears the value of cell A1.}

You're not setting a var's value to the value of a cell, you're setting the cell's value to a blank var thereby erasing the cell's value. It should be filePath = Sheet2.Range("A1").Value, (the reverse of what you have above).

_{When I use wksht.paste to paste copied content to Sheet2, it just pastes all cell values without formatting.}

You're not just pasting between workbooks; you're pasting between workbooks open in separate application instances. You lose detail like formatting when pasting across instances. In any event, the separate Excel.Application seems wholly unnecessary.

Option Explicit

Sub Button1_Click()
    ' define variables
    Dim lastRow As Long
    Dim wbk As Workbook
    Dim filePath As Variant

    'on error statement
    On Error GoTo errHandler:

    ' Select file path
    filePath = Sheet2.Range("A1").Value
    Set wbk = Workbooks.Open(filePath)
    'Set wbk = Workbooks.Open("C:\Users\Personal\Documents\Excel files\Dummy-Data - Copy.xlsx")

' Copy contents & Paste contents
    Application.ScreenUpdating = False

    lastRow = wbk.Sheets(7).Range("A" & Rows.Count).End(xlUp).Row
    wbk.Sheets(7).Range("A2:Q" & lastRow).Copy _
        Destination:=Sheet2.Range("A2")

    'shouldn't have to disable alerts
    'Application.DisplayAlerts = False
    wbk.Close savechanges:=False
    'Application.DisplayAlerts = True
'
    Application.ScreenUpdating = True
    Exit Sub

'error block
errHandler:
    MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
        & Err.Number & vbCrLf & Err.Description & vbCrLf & _
        "Please follow instruction sheet"

End Sub

The naked worksheet codename references should be valid within ThisWorkbook.