PDA

View Full Version : Sleeper: Problem with range selection



starsky
08-25-2009, 07:31 AM
Hi,

I've created a workbook that will collate data from a number of other workbooks. The code for this purpose is working on the whole. The macro opens and extracts data (below header row) from all workbooks in a specific folder.

One problem I am encountering is when there is only a single used row below the header row. Excel won't select just that row but all blank rows too. Subsequently the data is not being transfered in this instance.

This is the code I'm using so far. 'Master' is the workbook to which code is being pasted.


Range("A12:I12").Select
Range(Selection, Selection.End(xlDown)).Copy
Workbooks("Master").Sheets("Data").Activate
Range("A2").Select
'finds next blank row, pastes, etc
If Range("A2") = "" Then
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
With ActiveCell
Cells(.Row + 1, .Column).Select
End With
ActiveSheet.Paste
End If

Any tips would be great.

Thanks.

p45cal
08-25-2009, 08:50 AM
This one liner might work, it uses a different method to try to establish where to copy from/to. So depending on what data are already elsewhere in your sheets it might choose the right ranges.

Intersect(Range("A12:I" & Rows.Count), Range("A12").CurrentRegion).Copy _
Workbooks("Master.xls").Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Offset(1)


I had to add '.xls' to the name of the Master workbook name in the code above, so I assume your workbook doesn't have this extension?

mdmackillop
08-25-2009, 08:56 AM
Sub CopyToMaster()
Dim Rw As Long
Dim Tgt As Range
'Find next empty row
Set Tgt = Workbooks("Master.xls").Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Offset(1)
'Find last data row
Rw = Range("A:L").Find("*", after:=Range("A1"), searchorder:=xlRows, SearchDirection:=xlPrevious).Row
'Copy Data to Target
Range("A12:I" & Rw).Copy Tgt
End Sub