Working Macro Needs Slight Tweak

[Sunvisor]

Im working on a macro that goes into a bunch of spreadsheets we save located on my computer - It copies and then pastes a specific range into another sheet.

We collect a lot of data from e-mails in a template that we created so it comes back the same...the only difference is the range...They can enter in anywhere from 1 to 30 lines of data...but it all starts at "A5"

I was just wondering if there was a way to make it able to see if Blank --- stop? That way I can set a large range but not worry about getting so many blanks...also column A would be the primary key that if blank then stop...

(If this isn't possible I can always just filter out blanks when I am finished compiling the records)


THANKS!!!

[/FONT]
[FONT=Courier New]Sub MRcollect_Click()[/FONT]
[FONT=Courier New]Dim path As String[/FONT]
[FONT=Courier New]Dim FileName As String[/FONT]
[FONT=Courier New]Dim Wkb As Workbook[/FONT]
[FONT=Courier New]Dim wsmf As Worksheet[/FONT]
[FONT=Courier New]Dim lngLastRow1 As Long[/FONT]
[FONT=Courier New]Dim wkb1 As Workbook[/FONT]
[FONT=Courier New]Dim rng As Range[/FONT]
[FONT=Courier New]    [/FONT]
[FONT=Courier New]Call ToggleEvents(False)[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]    '###################################[/FONT]
[FONT=Courier New]    path = "C:\Documents and Settings\M08040\My Documents\Test1"  'Change as needed (P's computer)[/FONT]
[FONT=Courier New]    '###################################[/FONT]
[FONT=Courier New]    FileName = Dir(path & "\*.xls", vbNormal)[/FONT]
[FONT=Courier New]    [/FONT]
[FONT=Courier New]    Do Until FileName = ""[/FONT]
[FONT=Courier New]        [/FONT]
[FONT=Courier New]        'this opens the workbook in the above specified folder[/FONT]
[FONT=Courier New]        Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)[/FONT]
[FONT=Courier New]        [/FONT]
[FONT=Courier New]        'set this to the sheet number to look at[/FONT]
[FONT=Courier New]        Set wsmf = Wkb.Sheets(1) ' Use this line if want first sheet every time[/FONT]
[FONT=Courier New]        [/FONT]
[FONT=Courier New]        'moves to next spreadsheet if no "Job Description" is not found[/FONT]
[FONT=Courier New]        'on error goto NotFound[/FONT]
[FONT=Courier New]        [/FONT]
[FONT=Courier New]        'finds job description[/FONT]
[FONT=Courier New]        Range("A5:N19").Copy[/FONT]
[FONT=Courier New]        [/FONT]
[FONT=Courier New]        'pastes the information in the last row of your spreadsheet[/FONT]
[FONT=Courier New]        Windows("MRCollectionApril.xls").Activate[/FONT]
[FONT=Courier New]        Set ws = ActiveWorkbook.Sheets(1)[/FONT]
[FONT=Courier New]        lngLastRow1 = ws.Range("A65536").End(xlUp).Row + 1[/FONT]
[FONT=Courier New]        Range("A" & lngLastRow1).PasteSpecial xlPasteValues[/FONT]
[FONT=Courier New]        Application.CutCopyMode = False[/FONT]
[FONT=Courier New]    [/FONT]
[FONT=Courier New]NotFound:[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]    FileName = Dir()[/FONT]
[FONT=Courier New]    [/FONT]
[FONT=Courier New]    Wkb.Close[/FONT]
[FONT=Courier New]    [/FONT]
[FONT=Courier New]    Loop[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]Call ToggleEvents(True)[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]End Sub[/FONT]
[FONT=Courier New]Sub ToggleEvents(blnState As Boolean)[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]'Originally written by firefytr[/FONT]
[FONT=Courier New]    [/FONT]
[FONT=Courier New]    With Excel.Application[/FONT]
[FONT=Courier New]        .DisplayAlerts = blnState[/FONT]
[FONT=Courier New]        .EnableEvents = blnState[/FONT]
[FONT=Courier New]        .ScreenUpdating = blnState[/FONT]
[FONT=Courier New]        If blnState Then .CutCopyMode = False[/FONT]
[FONT=Courier New]        If blnState Then .StatusBar = False[/FONT]
[FONT=Courier New]    End With[/FONT]
[FONT=Courier New]    [/FONT]
[FONT=Courier New]End Sub[/FONT]
[FONT=Courier New]