Copy Paste as Excel using VBA code

[Rfriend]

Hello,


I have an inventory of equipment, radios, and vehicles that is updated by many users. This update is sent either in total or in part to a fleet manager for insurance purposes. I have set up a user form for the department heads to use when an update is required. The user form allows the person to search for inventory to be updated using a listbox and header search via dropdown, and text search criteria, to get the equipment or listing they need for their departments. I am looking for some code that will allow me to copy a paste a table or print area from one worksheet to a new workbook that the user can save as an excel file through a command button.

I would also like to have the option to save specific data in excel format to a specific file that overwrite the any existing excel date and automatically attach to an outlook email.

I can export save as xlTypePDF all day, but have not been able to adapt that to keep the original excel format of the file as an excel file rather than PDF file.

Anyone have any thoughts on these two items?

This is the code I was trying to build from a couple different tutorials. It will copy and paste to new workbook, but this errors. This is as far as I got. Your input is greatly appreciated.

Sub SaveFile()
    Dim wbMaster As Workbook
    Dim wbLocal As Workbook
    Dim RadiosSH As Worksheet
    Dim masterNextRow As Long
      Application.DisplayAlerts = False
'    Dim Radio_InventorySH As ws
'        Set Radio_InventorySH = Sheet1

    Set wbMaster = Workbooks.Open("I:\Common\PWORKS1\WWW Operations\Josh\Fleet...Trans\Trailers\Radio Inventory Saved Copy.xlsm")
    Set RadiosSH = Sheet4
    Set wbLocal = ActiveWorkbook.Worksheets("Radios").Range("B9:E")
        masterNextRow = wbMaster.Worksheets("Radio_Inventory").Range("C9:E112") ' this is where I get an error
        wbMaster.Worksheets("Radio_Inventory").Cells(masterNextRow, 1).Value = wbLocal.Worksheets("Radios").Range("B9:E112").Value
        wbMaster.Worksheets("Radio_Inventory").Cells(masterNextRow, 2).Value = wbLocal.Worksheets("Radios").Range("B9:E112").Value
        wbMaster.Worksheets("Radio_Inventory").Cells(masterNextRow, 3).Value = wbLocal.Worksheets("Radios").Range("B9:E112").Value
        wbMaster.Worksheets("Radio_Inventory").Cells(masterNextRow, 4).Value = wbLocal.Worksheets("Radios").Range("B9:E112").Value
   
        Application.DisplayAlerts = True

 

[Rfriend]

Another piece of code I am trying is the PDF code. I am trying to change the save as PDF to xlxs file format. I have been able to get the file to save as xlxs but not attach to the email

Sub SaveEmail()
    Dim Opendialog
    Dim MyRange As Range
    Application.ScreenUpdating = False
        Opendialog = Application.GetSaveAsFilename("", filefilter:="xlxs Files (*.xlsm), *.xlsx", _
        Title:="Mobile_Equipment_Update")
    If Opendialog = False Then
        MsgBox "The operation was not successful"
        Exit Sub
    End If
        Sheet4.Select
    With Sheet4
        .Range("K1:N" & Cells(Rows.Count, "K").End(xlUp).Row).Name = "PDFRng"
    End With
         Set MyRange = Sheet4.Range("PDFRng")
        Sheet4.PageSetup.PrintArea = "PDFRng"
      On Error Resume Next
    
        MyRange.ExportAsFixedFormat Type:-XlFixedFormatType, Filename:=Opendialog, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False      [B][U] 'I ERROR HERE[/U][/B]
       
    On Error GoTo 0
        ActiveSheet.DisplayPageBreaks = False
        Application.ScreenUpdating = False
    Sheet1.Select ' Interface Sheet1
End Sub