How to create a loop and always move to the next used cell in VBA

Question

I am copying data from one worksheet into another, transposing and auto-filling using a macro but i want to create a loop and have it always select the next cell ("A8" & "B8")in the previous worksheet (MF2) to be copied instead of having to manually change the cell. This is the macro i'm using

Sub Test1()

    Sheets("MF2").Select
    Range("A8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("A").Find("", Cells(Rows.Count, "A")).Select
    ActiveSheet.Paste
    Sheets("MF2").Select
    Range("B8").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("B").Find("", Cells(Rows.Count, "B")).Select
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

    Dim endRow As Long
    endRow = Cells(Rows.Count, "B").End(xlUp).Row
    Range("A1").End(xlDown).Select
    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":A" & endRow)
    Range("C1").End(xlDown).Select
    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":C" & endRow)

End Sub

Can you give us a graphical representation of your data. It looks like you are getting data of different shapes and copying it. Do you have the data in rows with a row heading and transposing it to Columns with column headings? — Mr. Mascaro, Sep 22 '14 at 19:25
The data has no heading. The data being copied ("A8") is one single cell and ("B8") is multiple rows which is transpose to a column. — kenneth calliste, Sep 24 '14 at 18:00

score 0 · Answer 1 · answered Sep 23 '14 at 17:56

0

A couple of things. First, don't activate and select everything. Use variables to store references to your source and destination. Second, Range.Copy takes an optional destination parameter. You can copy and paste in one fell swoop. Here's the code.

Sub Test2()

    Dim sourceSheet As Worksheet
    Set sourceSheet = Sheets("MF2")

    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range("A8")

    Dim destSheet As Worksheet
    Set destSheet = Sheets("Sheet3")

    Dim destRange As Range
    With destSheet
        Set destRange = .Columns(1).Find("", .Cells(.Rows.Count, 1))
    End With

    sourceRange.Copy destRange

End Sub

And yes, I realize this answers exactly none of your questions.

answered Sep 23 '14 at 17:56

RubberDuck

11,933
4
50
95

probably best to just leave a comment pointing to the (relatively) canonical: [How to avoid using Select/Activate in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – David Zemens Sep 23 '14 at 18:20
1

Yeah @DavidZemens, maybe, but this way I know he actually saw how it's done. – RubberDuck Sep 23 '14 at 19:58
Well, it does answer the question `How to create a loop and always move to the next used cell in VBA` in some ways, I don't see any reason why this is downvoted. – dan Sep 23 '14 at 20:29
I understand to issue with using Select/Activate so i'll recreate the macro using your suggestions. Thanks – kenneth calliste Sep 24 '14 at 18:03

How to create a loop and always move to the next used cell in VBA

1 Answers1