Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro moves data and more!!!
I am using the following macro to move data from one sheet to another, call it from A to B. The problem is that it doesn't just move the 500 rows of data from A, it also appears to move every blank row below the data which lists as zeroes when the data is moved over to B. This complicates a second macro which sorts the data in sheet B. Any idea on how to get this macro to only move the data presented in A (the rows will change with time so the macro must be flexible enough to grab the data that is in A no matter how large or small)? Can't thank you guys enough. Sub STEP1_MovingOriginalData() Dim Rng As Range With Worksheets("M_Original_Data") Set Rng = .Range(.Cells(2, 1), .Cells(1, 1).End(xlDown)) End With With Worksheets("M_Original_Data") Range(Rng.Address).FormulaR1C1 = "=A_Original_Data!R[-1]C" End With Columns("B:B").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=524796 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro moves data and more!!!
If your data is contiguous you could try the command line:
Selection.CurrentRegion.Select Which is the VBA equivalent of Ctrl+Shift+*, using the asterisk at the top of the keyboard, or Ctrl + * using the asterisk on the NumPad -- Kevin Backmann "Sandeman" wrote: I am using the following macro to move data from one sheet to another, call it from A to B. The problem is that it doesn't just move the 500 rows of data from A, it also appears to move every blank row below the data which lists as zeroes when the data is moved over to B. This complicates a second macro which sorts the data in sheet B. Any idea on how to get this macro to only move the data presented in A (the rows will change with time so the macro must be flexible enough to grab the data that is in A no matter how large or small)? Can't thank you guys enough. Sub STEP1_MovingOriginalData() Dim Rng As Range With Worksheets("M_Original_Data") Set Rng = .Range(.Cells(2, 1), .Cells(1, 1).End(xlDown)) End With With Worksheets("M_Original_Data") .Range(Rng.Address).FormulaR1C1 = "=A_Original_Data!R[-1]C" End With Columns("B:B").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=524796 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro moves data and more!!!
Beware of End(xlDown) as it stops at any single cell in that column that's
empty (and of course the data could continue on from there in other columns). I always think this kind of thing is more safely captured with the current region: Range("A1").CurrentRegion.Select If you want to exclude headings, follow this with: Selection.Offset(1,0).Select Ok if you're being pedantic, this does select an extra blank row beneath the data so you can follow this with: Selection.Resize(Selection.Rows.Count-1).Select before copying and pasting as before. "Sandeman" wrote: I am using the following macro to move data from one sheet to another, call it from A to B. The problem is that it doesn't just move the 500 rows of data from A, it also appears to move every blank row below the data which lists as zeroes when the data is moved over to B. This complicates a second macro which sorts the data in sheet B. Any idea on how to get this macro to only move the data presented in A (the rows will change with time so the macro must be flexible enough to grab the data that is in A no matter how large or small)? Can't thank you guys enough. Sub STEP1_MovingOriginalData() Dim Rng As Range With Worksheets("M_Original_Data") Set Rng = .Range(.Cells(2, 1), .Cells(1, 1).End(xlDown)) End With With Worksheets("M_Original_Data") .Range(Rng.Address).FormulaR1C1 = "=A_Original_Data!R[-1]C" End With Columns("B:B").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=524796 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel macro which prompts for input and moves to a cell - repeated | Excel Worksheet Functions | |||
Macro or Formula that copies and moves data? | Excel Discussion (Misc queries) | |||
calc locks up after running a macro that moves sheets to a new fil | Excel Discussion (Misc queries) | |||
Make more info appear when mouse moves over a macro command button | Excel Programming | |||
Macro That Moves Cursor Down | Excel Programming |