Control down with Macro

[feejo]

I record a macro that copy a portion of a tab to another, but when my data changes the destination get data one above the other. I think it is because instead of sending Control Donw it goes to the cell that I recorded the macro with.

I probably just need to send Control downkey.

Range("C22:E22").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("archive.xls").Activate
    Sheets.Add
    Range("A2").Select
    ActiveSheet.Paste
    Windows("T&S2 5X 2000.xls").Activate
    ActiveWindow.ScrollRow = 1
    Range("K8:M19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A11").Select
    Selection.End(xlDown).Select
    Range("A190").Select
    Windows("T&S2 5X 2000.xls").Activate
    Sheets("TS-3").Select
    Range("C22:E22").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    ActiveSheet.Paste
    Range("D190").Select
    Windows("T&S2 5X 2000.xls").Activate
    Range("K8:M19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Windows("T&S2 5X 2000.xls").Activate
    Sheets("TS-2").Select
    Range("C22").Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Range("C22:E22").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    Range("A190").Select
    Selection.End(xlDown).Select
    Range("A216").Select
    ActiveSheet.Paste
    Range("D216").Select
    Windows("T&S2 5X 2000.xls").Activate
    Range("G40").Select
    Selection.End(xlUp).Select
    Range("K8:M19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A216").Select
    Selection.End(xlDown).Select
    Range("A245").Select
    Windows("T&S2 5X 2000.xls").Activate
    Sheets("TS-1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    Range("D245").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A245").Select
    Windows("T&S2 5X 2000.xls").Activate
    Range("C22:E22").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    ActiveSheet.Paste
    Windows("T&S2 5X 2000.xls").Activate
    Sheets("T&S Today").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    Selection.End(xlDown).Select
    Range("D295").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A295").Select
    Windows("T&S2 5X 2000.xls").Activate
    Range("C22:E22").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("archive.xls").Activate
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    Range("A308").Select
    Windows("T&S2 5X 2000.xls").Activate
    Windows("archive.xls").Activate
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("A294").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "10/6/2007"
    Range("A295").Select
End Sub 

[VBA Noob]

Is this what your after

Please Login or Register  to view this content.

Selects the next cell down from activecell.

or

Please Login or Register  to view this content.

Selects the last used cell in Column A

You may also found this link helpful

http://www.rondebruin.nl/copy1.htm

VBA Noob

[feejo]

Ok I change my script, so it goes like this.

Now it move on the same sheet the data, except it will alway's move it to those coordinates and not according to the right size of data.

Windows("archive.xls").Activate
    Range("H2:L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("G2").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    Range("A66").Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("C2").Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Range("N2:R2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    Range("A141").Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlToRight).Select
    Range("T2:X2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("S2").Select
    Selection.End(xlDown).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Range("A246").Select
    ActiveSheet.Paste
    Range("B246").Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlToRight).Select
    Range("Z1:AD1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("X1").Select
    Selection.End(xlDown).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    Range("A295").Select
    ActiveSheet.Paste
    Range("A294").Select
End Sub 

[VBA Noob]

See if link helps

http://www.ozgrid.com/VBA/ExcelRanges.htm

VBA Noob

[royUK]

Why do you have so many duplicate lines in your code & why bother with the Selecting? State clearly what you are copying & where to, your existing code needs a lot of editing.

[feejo]

To BoyUk: when I just paste it there is data mising, it require a special paste.

[VBA Noob]

This selects the last used cell in Row 1

Please Login or Register  to view this content.

VBA Noob

[royUK]

This is part of what I mean, the Selection.End(xlToLeft) is duplicated

Please Login or Register  to view this content.

[feejo]

After finding the
Find the last used cell, before a blank in a Column:

How can I go down 3 cells?

[VBA Noob]

I assume you have code to find the last used cell so just do

last used cell + 3

VBA Noob

[feejo]

[feejo]

Even if I tell him to go on the last cell+3 the line just after tell

Range("K19:L19").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("K8:L19").Select
    Range("K19").Activate 

So it will select there to copy. I am stuck.

[feejo]

This is part of what I mean, the Selection.End(xlToLeft) is duplicated

Please Login or Register  to view this content.

Ok I know nothing of VB, I just post the macro recorder.