Adding / Updating Information into a Range

[skarnik01]

Hi,

Please help me with a macro for my following requirment -
I enter value into cells A1, A2, A3 as -
A B C
When I click on ADD command button -
the values should get added into 1st non-blank cells in the columns A, B & C on Sheet 2 as -
1 2 3
4 5 6
7 8 9
A B C
Next time when I enter D, E, F in the same cells A1, A2, A3 and click on ADD button, it should get added as -
1 2 3
4 5 6
7 8 9
A B C
D E F
Now, if I want to update this last record as X, Y, Z instead of D, E, F, on clicking on UPDATE Command button, the values should get updated as -
1 2 3
4 5 6
7 8 9
A B C
X Y Z
Next time when I again ADD a record, it should get appended below the above mentioned values and so on.

Thanks in advance,
SK

 

[Luke M]

Here you go. Modify the Range and worksheet names as needed. Note 2 different macros for 2 different buttons.

Sub AddRecord()
Dim myRange As Range
Dim lastRow As Long

'Which cells get copied?
Set myRange = Worksheets("Sheet1").Range("A1:A3")


Application.ScreenUpdating = False
With Worksheets("Sheet2")
    'Find the last row
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  
    myRange.Copy
    .Cells(lastRow + 1, "A").PasteSpecial Paste:=xlPasteValues, Transpose:=True
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
  
End Sub

Sub UpdateRecord()
Dim myRange As Range
Dim lastRow As Long

'Which cells get copied?
Set myRange = Worksheets("Sheet1").Range("A1:A3")


Application.ScreenUpdating = False
With Worksheets("Sheet2")
    'Find the last row
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  
    myRange.Copy
    .Cells(lastRow, "A").PasteSpecial Paste:=xlPasteValues, Transpose:=True
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
  
End Sub

 

[Hui]

I would add two subroutines to the Code Module of the worksheet you are using

Sub Add()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

Range("A1:C1").Copy Range(Cells(lr + 1, 1), Cells(lr + 1, 3))
Range("A1:C1").ClearContents

End Sub

Sub Update()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

Range("A1:C1").Copy Range(Cells(lr, 1), Cells(lr, 3))
Range("A1:C1").ClearContents

End Sub

 

[skarnik01]

Thanks Luke and Hui, it was very helpful. Just a point -
I use Excel 2010. When I try to save the file with Macro, it saves as .xltm. Is it still possible to save it as .xlsx without impacting the Macro?

Thanks again !

 

[Debraj]

xlsx are designed to work without macro..
To use the macro.. you must have to save it in any Programming enable format.. like "macro Enable Workbook (.xlsm)" or "Binary Workbook (.xlsb)"

otherwise you will loose the Programming facility..

 

[Luke M]

To add to what Deb said, .xltm will also work, as that is a Macro-Enabled template. If you save as .xlsx, the macro will get stripped out.