Add a another constant/column to this macro?

Question

What this does now is take whats inputted Columns A:E and add whatever you list in column F, at the end of it, keeping A:E constant. This makes it much easier rather than copying and pasting but i want to add another row so that A:F is constant, switching the list to column G.

For ex, once it's outputted,

A1,B1,C1,D1,E1,F1
A1,B1,C1,D1,E1,F2
A1,B1,C1,D1,E1,F3
etc.

I just want to add another column to make it

A1,B1,C1,D1,E1,F1,G1
A1,B1,C1,D1,E1,F1,G2
A1,B1,C1,D1,E1,F1,G3

This is what I have so far.

Dim LastRowIput As String
    With Sheets("Input")
    LastRowInput = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With


For I = 2 To LastRowInput

Dim LastRowLoc As String
    With Sheets("Output")
    LastRowLoc = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
    End With


    Sheets("Input").Select
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Output").Select
    Range("F" & LastRowLoc).Select
    ActiveSheet.Paste
    Sheets("Input").Select
    Range("A" & I & ":" & "E" & I).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Output").Select

    Dim LastRow As String
    With ActiveSheet
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
    End With

    Range("A" & LastRow).Select
    ActiveSheet.Paste

    Dim LastRowLoc2 As String
    With Sheets("Output")
    LastRowLoc2 = .Cells(.Rows.Count, "F").End(xlUp).Row
    End With

    Application.CutCopyMode = False
    Range("A" & LastRow & ":" & "E" & LastRowLoc2).Select
    Selection.FillDown

    Sheets("Input").Select

Next I

A quick note, which may help you understand how it's working, is to [avoid using `.Select`/`.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — BruceWayne, Mar 30 '16 at 20:13
`Dim LastRowIput As String` or `Dim LastRowInput As Long` ? (note both spelling and variable type) — , Mar 30 '16 at 20:13
That's what I tried. Doesn't work, it just uses F as the variable as well — sng428, Mar 30 '16 at 20:35
I don't understand the loop. You take all of F from Input and put it into Output starting at the first available row. Then you copy a single row of A:E and do the same thing and fill down to the extent of F. But then you loop through and do the same thing with the next row. Why you are looping makes no sense to me. — , Mar 30 '16 at 20:55

score 0 · Answer 1 · answered Mar 30 '16 at 21:20

It seems that you want to copy the rows from A:G from Input to Output, expanding A:F in Output for every row in G.

Dim i As Long, lastRowInput As Long, nextRowOutput As Long
Dim wso As Worksheet

Set wso = Worksheets("Output")

With Sheets("Input")
    lastRowInput = .Cells(.Rows.Count, "C").End(xlUp).Row

    For i = 2 To lastRowInput
        nextRowOutput = wso.Cells(.Rows.Count, "G").End(xlUp).Row + 1
        .Range(.Cells(2, "G"), .Cells(2, "G").End(xlDown)).Copy _
          Destination:=wso.Cells(nextRowOutput, "G")
        .Range("A" & i & ":" & "F" & i).Copy _
          Destination:=wso.Range(wso.Cells(nextRowOutput, "A"), _
                                 wso.Cells(wso.Cells(.Rows.Count, "G").End(xlUp).Row, "F"))
    Next i
End With

I've removed all methods involving the Range .Select and Range .Activate methods in favor of direct referencing.

^{Sample data from Input worksheet}

^{Sample results from Output worksheet}

Add a another constant/column to this macro?

1 Answers1