2 Part Macro (Insert Row + Transpose)

**cschmid1** · 05-31-2012, 08:59 AM

Hey excel maestros,

I've scoured the posts looking for an answer to this question and have had some trouble (mainly because I'm so new to macros, outside of recording them, that I don't know how to modify them).

I've attached the excel sheet I'm working with.

What I'm looking to do is have a person enter data on the "Entry" worksheet, have the ability to press a button that will then insert a row below the last line of data in the "Data" sheet. Alternatively, I have formatted the Data sheet as a table so row insertion isn't as necessary as copying the data entered to the right of the cells in the "Entry" tab and pasting it below the last line of data in the "Data" sheet.

I know how to do this with a recorded macro, but then the location is locked. I'd be stuck entering/overwriting data every time I ran the macro.

Please help! Or direct me to the correct thread/forum

**arlu1201** · 05-31-2012, 09:22 AM

Try this code

 
Sub update_data()
Dim ws As Worksheet
Dim lrow As Long

Set ws = Worksheets("Entry")

With Worksheets("Data")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A" & lrow + 1).Value = ws.Range("C2").Value
    .Range("C" & lrow + 1).Value = ws.Range("C3").Value
    .Range("D" & lrow + 1).Value = ws.Range("C4").Value
    .Range("E" & lrow + 1).Value = ws.Range("C5").Value
    .Range("J" & lrow + 1).Value = ws.Range("C6").Value
    .Range("K" & lrow + 1).Value = ws.Range("C7").Value
    .Range("L" & lrow + 1).Value = ws.Range("C8").Value
    .Range("M" & lrow + 1).Value = ws.Range("C9").Value
    .Range("N" & lrow + 1).Value = ws.Range("C10").Value
    .Range("O" & lrow + 1).Value = ws.Range("C11").Value
End With

ws.Range("C2:C11").ClearContents

End Sub

It will copy the contents and delete the range C2:C11 in the Entry sheet for the next entry.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.