Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data ranges in a macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro error - copying data twice | Excel Discussion (Misc queries) | |||
macro error - copying data twice | Excel Discussion (Misc queries) | |||
Macro for copying conditional data | Excel Worksheet Functions | |||
Macro Help - Copying and appending data | Excel Worksheet Functions | |||
Copying ranges of data between worksheets | Excel Worksheet Functions |