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.
' 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
' A new workbook has been created and is ready for import of data.
' 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 Workbook
' INPUT FILE
Set wbkInput = Workbooks.Open(strInputFolder & strInputSheet)
'TODO: Check if "Results" tab exists
'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
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Set wbkInput = Nothing
'Set wbkNew = Nothing
Set ws = Nothing