Macro moves data and more!!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandeman - 21 Mar 2006 16:06 GMT

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

Signature

Sandeman

Reply to this Message

Kevin B - 21 Mar 2006 16:41 GMT

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

Signature

Kevin Backmann

> 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
[quoted text clipped - 22 lines]
> :=False, Transpose:=False
> End Sub

Reply to this Message

Martin - 21 Mar 2006 16:51 GMT

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.

Reply to this Message