Copying data ranges in a macro

  • Thread starter Thread starter Chris B.
  • Start date Start date
C

Chris B.

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
 
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. said:
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
Click to expand...
 
Back
Top