Copying data ranges in a macro
One way is to use an array formula in your code that determines the last row
with a value different from "".
Option Explicit
Sub testme01()
Dim LastRow As Long
Dim wks As Worksheet
Dim toWks As Worksheet
Dim destCell As Range
Set toWks = Worksheets.Add
For Each wks In ActiveWorkbook.Worksheets
With wks
If .Name = toWks.Name Then
'do nothing
Else
LastRow = .Evaluate("Max(Row(B10:ck343)*(b10:ck343<""""))")
If LastRow = 0 Then
'nodata
Else
With toWks
Set destCell = .Cells(.Rows.Count, "B").End(xlUp)
End With
If IsEmpty(destCell) Then
'do nothing
Else
Set destCell = destCell.Offset(1, 0)
End If
.Range("B10:ck" & LastRow).Copy _
Destination:=destCell
End If
End If
End With
Next wks
End Sub
If you put this formula in a cell on your worksheet:
=MAX(ROW(B10:CK343)*(B10:CK343<""))
but hit ctrl-shift-enter instead of just enter (it's an array formula), you'll
see that it evaluates to the last row that looks filled.
I used column B as my "main" column. I expect something there first.
"Chris B." wrote:
Here is my problem. I have 15 pages of data entry that
use cells B10-ck343 on each page. The cells are full of
vlookup's and equations if data is entered. I am trying
to write a macro that will copy only the rows I have
entered data into on the 15 pages unto 1 page for export
to access. the same number of rows will be on each page.
The problem is that I can not figure out how to copy and x
number of rows instead of the entire block. Right now I
only have data entered in rows 10-14 but copying the cells
with a [end][downarrow] picks up the blank formula rows as
well. HELP Please!
Chris
--
Dave Peterson
|
