Code to select all used cells on a sheet

[richanor]

Hi, I've combined a few bits of code from this board to come up with a macro that will combine a number of workbooks from the same directory into one master workbook. I just need help with selecting the range.

I am trying to select all used cells on the active sheet, except the column headings (Column Headings are rows 1-10, main data starts at row 11). At the moment my code for selecting the range is:

ws.Range("A11:E17").Copy

because in that worksheet there happend to be 7 rows of data. It will always be columns A:E, starting at row 11, but the number of rows will vary. Can somebody help me out with the code I need?

Thanks

Rich

 

[mumps]

I tried to tidy up your code a bit. See if this works for you:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim counter As Long
    counter = 0
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Admin\Annual Leave"
    ChDir strPath
    strExtension = Dir("*.xl*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            .Sheets("Tracker").Range("A11:E" & .Sheets("Tracker").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).Copy
            wkbDest.Sheets("Annual Leave Tracker").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            counter = counter + 1
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
    wkbDest.Sheets("Annual Leave Tracker").Columns.AutoFit
    MsgBox counter & " workbooks consolidated. ", , "Consolidation Complete"
End Sub

 

[richanor]

wow thanks very much. i'm getting an error (run-time error 76 Path not Found)

at this line:

ChDir strPath

 

[richanor]

sorry mumps, just realised I had moved the file! i'll give it a try now!

 

[richanor]

That seems to work great. A couple of things: each time it closes a file is pauses to ask if I want to keep the data on the clipboard. Is there a way to say "no" automatically?

Also - when it opens the third file, it asks: Attendee Tracker.xlsm is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen Attendee Tracker.xlsm?

(Attendee Tracker is the master workbook which contains the sheet "Annual Leave Tracker)

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim counter As Long
    counter = 0
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Admin\Annual Leave"
    ChDir strPath
    strExtension = Dir("*.xl*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        If wkbSource.Name <> "Attendee Tracker.xlsm" Then
            With wkbSource
                .Sheets("Tracker").Range("A11:E" & .Sheets("Tracker").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).Copy
                wkbDest.Sheets("Annual Leave Tracker").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
                Application.CutCopyMode = False
                counter = counter + 1
                .Close savechanges:=False
            End With
        End If
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
    wkbDest.Sheets("Annual Leave Tracker").Columns.AutoFit
    MsgBox counter & " workbooks consolidated. ", , "Consolidation Complete"
End Sub

 

[richanor]

Hi Mumps, thanks you so much for your help and persistence! I'm still getting an error as it opens the third worrkbook in the folder - Attendee Tracker.xlsm is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen Attendee Tracker.xlsm? If I click No, I get the error:

Run-Time error '1004': Method 'Open' of object 'Workbooks' failed

If I click Yes, it reopens a fresh version of the file.

 

[richanor]

Ah - it's OK. I have just worked out that it was trying to combine the master sheet into the master sheet! I've amended it now - working a dream, thanks very much for all your help Mumps

Rich

 

[mumps]

You are very welcome.