How to copy a every part of a worksheet

[Pookiemeister]

There is a worksheet inside a workbook located on server that I need to copy.
The problem that I am stuck on is how to copy all the contents of the page including the header content, footer content, and margin settings(Top,Left, Bottom, Footer,Right, and Header) of the selection using VBA. Here is what I have so far. Thank You for any help or advice offered.

Option ExplicitPrivate Sub Workbook_Open()
        
        Dim sPath As String, sFile As String
        Dim wb As Workbook
        
        sPath = "J:\GRP\EVERYONE\FORMS\"
        sFile = sPath & "F-103-04 Exh I-Solid Dose Usage Record-Rev001.xlsx"
        Set wb = Workbooks.Open(sFile)
        call OpenSDUsageRecord

Sub OpenSDUsageRecord()    Cells.Select
    With Selection
        .Copy
        
    End With
End Sub

 

[Pookiemeister]

I decided to just take the current Margin Values and assign them to their appropriate Page Margin value. Hope that make sense. Here's the updated code.

Option ExplicitPrivate Sub Workbook_Open()
        
        Dim sPath As String, sFile As String
        Dim wb As Workbook
        
        sPath = "J:\GRP\EVERYONE\FORMS\"
        sFile = sPath & "F-103-04 Exh I-Solid Dose Usage Record-Rev001.xlsx"
        Set wb = Workbooks.Open(sFile)
        Call OpenSDUsageRecord

The problem is located in the code below:

Sub OpenSDUsageRecord()    
    Dim NewWB As New Workbook
    
    Set NewWB = Application.Workbooks("ChattemPackaging.xlsm").Worksheets("Solid Dose Usage Record")
    ActiveWorkbook.Copy NewWB
        NewWB.TopMargin = 0.3
        NewWB.LeftMargin = 0.2
        NewWB.BottomMargin = 0.3
        NewWB.FooterMargin = 0.2
        NewWB.RightMargin = 0.2
        NewWB.HeaderMargin = 0.2
        
End Sub

After Editing this code, I now get a "Runtime Error '13: Type Mismatch" and the following line of code is highlighted in yellow.

Set NewWB = Application.Workbooks("ChattemPackaging.xlsm").Worksheets("Solid Dose Usage Record")

Sorry for the late edit. Thank You

 

[Logit]

.
If you change :

[COLOR=#333333]Dim NewWB As New Workbook[/COLOR]

to

[COLOR=#333333]Dim NewWB As Workbook[/COLOR]

does the error go away ?

 

[MARK858]

Also NewWB in the line below

Set NewWB = Application.Workbooks("ChattemPackaging.xlsm").Worksheets("Solid Dose Usage Record")

is a Worksheet not a Workbook.

 

[Pookiemeister]

Logit- When I changed my code to your suggestion and deleted the word new, I'm still getting a runtime error 13 type mismatch.

Mark858-Basically yes. I am copying the contents from one worksheet in another workbook to another specific worksheet in that workbook.

I really hope that makes sense and sorry for the poor explanation. Thank you both for your help.

 

[Pookiemeister]

When I changed this line of code below, I now get "Runtime error '9: Subscript out of range"

Set NewWB = Worksheets("Solid Dose Usage Record")

Thank You

 

[MARK858]

You need to leave that line as it was originally but declare NewWB as Worksheet not Workbook or New Workbook.

 

[Pookiemeister]

Thank You Mark858. That fixed that problem but I still don't know how to format the NewWB worksheet with Margin values listed in the original above code and the code below. Because now I'm getting "Compile error: Method or data member not found" Thank you for all your help.

Sub OpenSDUsageRecord()    
    Dim NewWB As Worksheet
    
    Set NewWB = Application.Workbooks("ChattemPackaging.xlsm").Worksheets("Solid Dose Usage Record")
    ActiveWorkbook.Copy NewWB
        NewWB[COLOR=#0000ff].TopMargin[/COLOR] [COLOR=#0000ff]= [/COLOR]0.3
        NewWB.LeftMargin = 0.2
        NewWB.BottomMargin = 0.3
        NewWB.FooterMargin = 0.2
        NewWB.RightMargin = 0.2
        NewWB.HeaderMargin = 0.2
End Sub

".TopMargin =" is highlighted in blue after compile error is displayed.

 

[MARK858]

Maybe...

    With NewWB.PageSetup
        .TopMargin = 0.3
        .LeftMargin = 0.2
        .BottomMargin = 0.3
        .FooterMargin = 0.2
        .RightMargin = 0.2
        .HeaderMargin = 0.2
    End With