none
Use VBA to insert worksheets from one workbook to another RRS feed

  • Question

  • Hi. I'm exporting data from access into Excel workbooks. There are about 50 different reports, each with it's own Excel checklist template, and certain ones need to have an additional set of special instructions that are stored in a different workbook. The special instructions are updated periodically, and since I don't want to manually update 50 different spreadsheets every time there is an update I want to append the instructions as a separate tab in the checklist at run time.

    Here's where I'm at now:

        Dim ApXL As Object
        Dim xlChklstWBk As Object
        Dim xlChklstWSh As Object
        Dim xlSpInsWBk As Object
        Dim xlSpInsSh As Object
        Dim strChklistPath As String
        Dim strChklistName As String
        Dim strSpInsPath As String
        Dim strSpInsName As String
                       
        [logic to pick correct checklist and special instructions omitted]
                        
        Set ApXL = CreateObject("Excel.Application")
        Set xlChklstWBk = ApXL.workbooks.Open(fileName:=strChklistPath & strChklistName & ".xlsx")
        xlChklstWBk.worksheets("Validation Procedures").Activate  'this is the last tab in the checklist
     
        xlChklstWBk.worksheets.Add After:=xlChklstWBk.activesheet
        Set xlChklstWSh = xlChklstWBk.worksheets("Sheet1") ' this is the new sheet that was just inserted
        xlChklstWSh.Name = "Instructions"                   
        Set xlSpInsWBk = ApXL.workbooks.Open(fileName:=strSpInsPath & strSpInsName & ".xlsx")
        Set xlSpInsWSh = xlSpInsWBk.worksheets("Sheet1")
        xlSpInsWSh.range("A:J").copy
                       
        xlChklstWSh.Activate
        With xlChklstWSh.range("A:J")
               .PasteSpecial (xlPasteColumnWidths)
               .PasteSpecial (xlFormats)
               .PasteSpecial (xlValues)
        End With
                       
        xlSpInsWBk.Close                

        [additional logic to export data from access]

    It keeps generating errors when I try to paste. I would like to paste the formats and column widths as well as the values because the instructions have merged cells and other funky formatting, so it would be time consuming if I had to recreate this manually.

    What am I doing wrong here?

    Monday, March 2, 2015 4:58 PM

Answers

  • You cannot rely on the name of the newly added sheet being "Sheet1" so change

      xlChklstWBk.worksheets("Validation Procedures").Activate  'this is the last tab in the checklist
     
        xlChklstWBk.worksheets.Add After:=xlChklstWBk.activesheet
        Set xlChklstWSh = xlChklstWBk.worksheets("Sheet1") ' this is the new sheet that was just inserted
       

    to this one line             

        Set xlChklstWSh = xlChklstWBk.Worksheets.Add(After:=xlChklstWBk.Worksheets("Validation Procedures"))   

    You may have an "On Error Resume Next" line somewhere that prevents your code from breaking when you try to set the worksheet object to a non-existent sheet.  When coding, that is a bad idea unless you have a specific requirement when an error should be ignored, but you should always turn that off with "On Error Goto 0" 
    Monday, March 2, 2015 5:22 PM

All replies

  • You cannot rely on the name of the newly added sheet being "Sheet1" so change

      xlChklstWBk.worksheets("Validation Procedures").Activate  'this is the last tab in the checklist
     
        xlChklstWBk.worksheets.Add After:=xlChklstWBk.activesheet
        Set xlChklstWSh = xlChklstWBk.worksheets("Sheet1") ' this is the new sheet that was just inserted
       

    to this one line             

        Set xlChklstWSh = xlChklstWBk.Worksheets.Add(After:=xlChklstWBk.Worksheets("Validation Procedures"))   

    You may have an "On Error Resume Next" line somewhere that prevents your code from breaking when you try to set the worksheet object to a non-existent sheet.  When coding, that is a bad idea unless you have a specific requirement when an error should be ignored, but you should always turn that off with "On Error Goto 0" 
    Monday, March 2, 2015 5:22 PM
  • Thanks Bernie. I have an error handler set up to catch errors, but this line has never generated any. I appreciate the insight though.
    Monday, March 2, 2015 5:29 PM