Click here to Skip to main content
15,884,388 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
VB
Sub Consolidate()

Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, ws As Worksheet


'Setup
    Application.ScreenUpdating = False  'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False   'turn off system messages for now

    Set ws = ThisWorkbook.Sheets("sheet5")    'sheet report is built into

With ws
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        .UsedRange.Offset(1).EntireRow.Clear
        NR = 2
    Else
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'appends data to existing data
    End If

'Path and filename (edit this section to suit)
    fPath = "C:\Users\" 'remember final \ in this string
    fPathDone = fPath & " Validation\"     'remember final \ in this string
    On Error Resume Next
        MkDir fPathDone                 'creates the completed folder if missing
    On Error GoTo 0
    fName = Dir(fPath & "*.xls*")        'listing of desired files, edit filter as desired

'Import a sheet from found files

    For Each ws In wbData.Sheets(Array(" Component List", " Component", "Components"))
        LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row 'Find last row
        If NR = 1 Then 'copy the data AND titles
            ws.Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
        Else 'copy the data only
            ws.Range("A2:A" & LR).EntireRow.Copy .Range("A" & NR)
        End If
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
    Next ws


End With

ErrorExit:    'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
End Sub
Posted
Comments
CPallini 17-Jul-14 4:02am    
What is the offending line? Did you try to debug it?
CodeFear 18-Jul-14 2:04am    
Error is in: For Each ws In wbData.Sheets(Array(" Component List", " Component", "Components"))

1 solution

VB
    Set ws = ThisWorkbook.Sheets("sheet5")    'sheet report is built into

With ws

There is no check here that you actually have a "sheet5" in the current workbook.
 
Share this answer
 
Comments
CodeFear 18-Jul-14 2:06am    
Hi Richard,
Thanks for looking into it. But there is no problem in this statement.
Error is in:For Each ws In wbData.Sheets(Array(" Component List", " Component", "Components"))
Richard MacCutchan 18-Jul-14 4:32am    
Well we still cannot guess what is happening. You need to step through that code with your debugger to find out exactly when the variable is being used without a reference.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900