Click here to Skip to main content
15,895,142 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)



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