• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

excel vba copy paste workbook to new workbook

I've created a new workbook and now I want to open another excel file, copy the "Results" sheet, create a new sheet in my new workbook, and paste the data into it.
I can't activiate the new workbook after copying the "Results" sheet. I keep getting the "Subscript out of Range" error.  It seems to be some problem with the way I am trying to use the "Activate" method.
I have been wrastling with this since yesterday.  I'd really appreciate some help.
' Function:  inputFileData(strDestSheet, strCopyRange, strPasteRange, strInputFolder,strInputSheet)
'
'            Open an existing input file (xls) and copy the data from "Results" worksheet.
'            Open the new workbook, add a new worksheet corresponding to the input file,
'               and then copy the data into it.
'            Next time called, open another existing input file, copy the data from the
'            "Results" worksheet, create another new worksheet, and copy the data to it.
'
' Parameters:
'            strDestSheet - name of the new worksheet in new workbook
'            strCopyRange - range to copy from existing worksheet
'            strPasteRange - where to paste data in new worksheet
'            strInputFolder - folder where existing files are located
'            strInputSheet - the name of the worksheet that will be copied
'            strCompletePathFilename - the full path and filename of new workbook
'
'PreConditions
'
'            A new workbook has been created and is ready for import of data.
'
'PostConditions
'            The new workbook contains worksheets of data from all the existing input files.
'


Public Function inputFileData(strDestSheet As String, strCopyRange As String, strPasteRange As String, _
                        strInputFolder As String, strInputSheet As String, _
                        strCompletePathFilename As String) As Boolean
    
    Debug.Print (strCompletePathFilename)
    Dim wbkInput As Workbook
    Dim wbkNew As Workbook
    Dim ws As Workbook

       ' INPUT FILE
        Set wbkInput = Workbooks.Open(strInputFolder & strInputSheet)
            'TODO: Check if "Results" tab exists
        wbkInput.Sheets("Results").Activate
        
        'OPEN NEW REPORT
' ERRORS HERE:  "Subscript out of Range"
        Workbooks("Q:\DCS\SUPPORT\RC-T\20110331-Results\RC-T Results Report 20110512 105436").Activate
        ActiveWorkbook.Worksheets.Add.Name = strDestSheet
        
        ' COPY
        wbkInput.Worksheets("Results").Range(strCopyRange).Copy

        ' PASTE
        Sheets(strDestSheet).Range(strPasteRange).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
       
' Housekeeping
    
    wbkInput.Close
    'wbkNew.Close
    'ws.Close
    Set wbkInput = Nothing
    'Set wbkNew = Nothing
    Set ws = Nothing
    
End Function

Open in new window

0
Lambel
Asked:
Lambel
  • 5
  • 2
1 Solution
 
telyni19Commented:
Is "RC-T Results Report 20110512 105436" the full name of your new blank file, or do you have an Excel extension on it, like .xls? Did you create this new file before running the current import routine? And is it currently open when this routine starts?

It is better if it does not start open because then you can open it with a handle to keep track of it, like you are doing with your results file:

Set wbkNew = Workbooks.Open("Q:\DCS\SUPPORT\RC-T\20110331-Results\RC-T Results Report 20110512 105436.xls")

Then if you set wsNew as a worksheet variable, you can keep track of your new sheet the same way:

Set wsNew = wbkNew.Worksheets.Add
wsNew.Name = strDestSheet

You don't really need to activate the workbook to add your sheet. If you're not displaying the information, but closing the workbooks after copying the results over, then there's no need to activate anything. Skip the activate and refer to each workbook or worksheet of interest by variable name for everything.
0
 
LambelAuthor Commented:
telyni19:  
The full path and filename is:
Q:\DCS\SUPPORT\RC-T\20110331-Results\RC-T Results Report 20110512 105436.xls
Yes, this file was created before calling the function "inputFileData().  I took your advice and closed the workbook before calling the function.  

Now I'm having trouble with managing for when a sheet ("OH Count") in the new report above has input file data from two different input files.  I'm trying to set the focus - or activate the correct sheet - or however you would describe it (??) - so that the paste is pointed to the existing sheet.

Man, this is killing me.  Thanks for any help.
' Function:  inputFileData(strDestSheet, strCopyRange, strPasteRange, strInputFolder,strInputSheet)
'
'            Open an existing input file (xls) and copy the data from "Results" worksheet.
'            Open the new workbook, add a new worksheet corresponding to the input file,
'               and then copy the data into it.
'            Next time called, open another existing input file, copy the data from the
'            "Results" worksheet, create another new worksheet, and copy the data to it.
'
' Parameters:
'            strDestSheet - name of the new worksheet in new workbook
'            strCopyRange - range to copy from existing worksheet
'            strPasteRange - where to paste data in new worksheet
'            strInputFolder - folder where existing files are located
'            strInputSheet - the name of the worksheet that will be copied
'            strCompletePathFilename - the full path and filename of new workbook
'
'PreConditions
'
'            A new workbook has been created and is ready for import of data.
'
'PostConditions
'            The new workbook contains worksheets of data from all the existing input files.
'


Public Function inputFileData(strDestSheet As String, strCopyRange As String, strPasteRange As String, _
                        strInputFolder As String, strInputSheet As String, _
                        strCompletePathFilename As String) As Boolean
    
    Debug.Print (strCompletePathFilename)
    Dim wbkInput As Workbook
    Dim wbkNew As Workbook
    Dim ws As Worksheet
                
        ' COPY INPUT FILE
        Set wbkInput = Workbooks.Open(strInputFolder & strInputSheet)
            'TODO: Check if "Results" tab exists
        wbkInput.Worksheets("Results").Range(strCopyRange).Copy

        ' PASTE
            'check if sheet exists
            On Error Resume Next
            Set wbkNew = Workbooks.Open(strCompletePathFilename)
            Set ws = ThisWorkbook.Worksheets(strDestSheet)
                If Err > 0 Then
                 wbkNew.Worksheets.Add.Name = strDestSheet
                   Err.Clear
                Else
                   'do nothing
                End If
                On Error GoTo 0
        ' ERRORS HERE: "Select method of range class failed"
        Sheets(strDestSheet).Range(strPasteRange).Select
    
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
       
       
' Housekeeping
    
    Application.DisplayAlerts = False
        wbkInput.Close SaveChanges:=False
        wbkNew.SaveAs Filename:=strCompletePathFilename
        wbkNew.Close
        Set wbkInput = Nothing
    Application.DisplayAlerts = True
End Function

Open in new window

0
 
SANTABABYCommented:
Please check if the following version (modified) of your function helps:


Public Function inputFileData(strDestSheet As String, strCopyRange As String, strPasteRange As String, _
                        strInputFolder As String, strInputWbkName As String, _
                        strCompletePathFilename As String) As Boolean
    
    'Debug.Print (strCompletePathFilename)
    Dim wbkInput As Workbook
    Dim wbkOutput As Workbook
    Dim strOutputWbkName As String

       ' INPUT FILE
        Set wbkInput = Workbooks.Open(strInputFolder & strInputWbkName)
            'TODO: Check if "Results" tab exists
        'wbkInput.Sheets("Results").Activate
        
        'OPEN NEW REPORT
' ERRORS HERE:  "Subscript out of Range"
        strOutputWbkName = "Q:\DCS\SUPPORT\RC-T\20110331-Results\RC-T Results Report 20110512 105436.xls" 'or whatever
        'Workbooks("Q:\DCS\SUPPORT\RC-T\20110331-Results\RC-T Results Report 20110512 105436").Activate
        Set wbkOutput = Workbooks.Open(strOutputWbkName)
        wbkOutput.Worksheets.Add.Name = strDestSheet
        
        ' COPY
        wbkInput.Worksheets("Results").Range(strCopyRange).Copy

        ' PASTE
        wbkOutput.Sheets(strDestSheet).Range(strPasteRange).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
       
' Housekeeping
    
    wbkInput.Close
    wbkOutput.Close
    Set wbkInput = Nothing
    Set wbkOutput = Nothing
    
End Function

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LambelAuthor Commented:
@SANTABABY:  Does this check for the strDestSheet already existing (see my comment above)?  Can you tell me how to modify this so that it checks for an existing sheet before it creates a new one?

0
 
LambelAuthor Commented:
The second set of code I attached works through all the files until I come to a input file that needs to get added to an existing sheet in the wbkNew.  
0
 
LambelAuthor Commented:
telyni19:  
I think I've finally got it working correctly.  I'm disappointed with the speed - I have to save and close the wbkNew with each iteration in order to use the .open method to get the focus back on it with the next iteration.  But it's working!!

I've attached it for your fyi.  I haven't found an example of the copy-paste controlled by a third workbook that runs the code.
' Function:  inputFileData(strDestSheet, strCopyRange, strPasteRange, strInputFolder,strInputSheet)
'
'            Open an existing input file (xls) and copy the data from "Results" worksheet.
'            Open the new workbook, add a new worksheet corresponding to the input file,
'               and then copy the data into it.
'            Next time called, open another existing input file, copy the data from the
'            "Results" worksheet, create another new worksheet, and copy the data to it.
'
' Parameters:
'            strDestSheet - name of the new worksheet in new workbook
'            strCopyRange - range to copy from existing worksheet
'            strPasteRange - where to paste data in new worksheet
'            strInputFolder - folder where existing files are located
'            strInputSheet - the name of the worksheet that will be copied
'            strCompletePathFilename - the full path and filename of new workbook
'
'PreConditions
'
'            A new workbook has been created and is ready for import of data.
'
'PostConditions
'            The new workbook contains worksheets of data from all the existing input files.
'


Public Function inputFileData(strDestSheet As String, strCopyRange As String, strPasteRange As String, _
                        strInputFolder As String, strInputSheet As String, _
                        strCompletePathFilename As String) As Boolean
    
    Dim wbkInput As Workbook
    Dim wbkNew As Workbook
    Dim ws As Worksheet
     If strDestSheet = "OH Count" Then MsgBox "test here"
     
        ' COPY INPUT FILE
        Set wbkInput = Workbooks.Open(strInputFolder & strInputSheet)
            'TODO: Check if "Results" tab exists
        wbkInput.Worksheets("Results").Range(strCopyRange).Copy

        ' PASTE
            'check if sheet exists
            On Error Resume Next
            Set wbkNew = Workbooks.Open(strCompletePathFilename)
            Set ws = ThisWorkbook.Worksheets(strDestSheet)
                If Err > 0 Then
                 wbkNew.Worksheets.Add.Name = strDestSheet
                   Err.Clear
                Else
                   'do nothing
                End If
                On Error GoTo 0
            Range(strPasteRange).Select
    
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
       
' Housekeeping
    
    Application.DisplayAlerts = False
    
      ' Delete the unused generic sheet1, sheet2, sheet3
    With Workbooks(strCompletePathFilename & ".xls")
        .Sheets("Sheet1").Delete
        .Sheets("Sheet2").Delete
        .Sheets("Sheet3").Delete
    End With

        wbkInput.Close SaveChanges:=False
        wbkNew.SaveAs Filename:=strCompletePathFilename
        wbkNew.Close
        Set wbkInput = Nothing
        Set ws = Nothing
    Application.DisplayAlerts = True

End Function

Open in new window

0
 
telyni19Commented:
Are you displaying all the changes as you do them? Once you're sure everything is working correctly, you could use
Application.ScreenUpdating = False
to avoid redrawing the screen while the code runs. This can considerably speed up worksheet manipulation. Just make sure to turn it back on with Application.ScreenUpdating = True in your housekeeping area, or the application won't visually respond once you're done.
0
 
LambelAuthor Commented:
telyni19:
Thanks - I'll do that.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now