none
console the file data issue RRS feed

  • Question

  • Hey team, I am calling the workbook to open (Data.xls) to add the consolidated worksheet in Data workbook getting error '9" subscript out of range, look into this. (TargetSh - sheet not added)

    Dim TargetSh As Worksheet
    Dim DestCell As Range
    Dim LastRow As Long
    Dim sh As Worksheet
    Dim wkk As Workbook

    Workbooks.Open Filename:="D:\Data.xls"

    On Error Resume Next

    Application.DisplayAlerts = False

    'if exist then delete it

    If (Sheets("Consolidated")) Then Sheets("Consolidated").Delete

    If (Sheets("Console Final")) Then Sheets("Console Final").Delete

    On Error GoTo 0


    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set wkk = Workbooks("Data")

    On Error Resume Next
    Set TargetSh = Worksheets("Consolidated")
    On Error GoTo 0
    If TargetSh Is Nothing Then
        Set TargetSh = Worksheets.Add(before:=Sheets(1))
        TargetSh.Name = "Consolidated"
    Else
        TargetSh.Cells.Clear
    End If
    Set DestCell = TargetSh.Range("A1")
    Sheets(2).Range("A1:BA1").Copy DestCell 'copy header
    Set DestCell = DestCell.Offset(1)

    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> "Consolidated" Then
            LastRow = sh.Cells.SpecialCells(xlCellTypeLastCell).Row
            sh.Range("A2", sh.Range("BA" & LastRow)).Copy Destination:=DestCell
            Set DestCell = DestCell.Offset(LastRow - 1)
        End If
    Next

    Wednesday, July 17, 2013 3:32 PM

All replies

  • Change

    Set wkk = Workbooks("Data")

    to

    Set wkk = Workbooks("Data.xls")

    and then be specific:

    Set TargetSh = wkk.Worksheets("Consolidated")

    or

    Set TargetSh = Thisworkbook.Worksheets("Consolidated")

    It is unclear which you mean. And, etc.

    And this

    If (Sheets("Consolidated")) Then Sheets("Consolidated").Delete

    If (Sheets("Console Final")) Then Sheets("Console Final").Delete

    can be just

    Sheets("Consolidated").Delete

    Sheets("Console Final").Delete



    Wednesday, July 17, 2013 4:33 PM
  • Oh.... i will manage it. thanks
    Thursday, July 18, 2013 6:46 PM