new record on new row line?

PANKAJ UTEKAR · Jun 7th 2011

Code

Private Sub CommandButton4_Click()
Range("e4").Select
ActiveCell.FormulaR1C1 = Range("c4")


Range("f4").Select
ActiveCell.FormulaR1C1 = Range("c13")


Range("g4").Select
ActiveCell.FormulaR1C1 = Range("c22")


Range("h4").Select
ActiveCell.FormulaR1C1 = Range("c24")


Range("i4").Select
ActiveCell.FormulaR1C1 = Range("c26")


Range("j4").Select
ActiveCell.FormulaR1C1 = Range("c27")
End Sub

Display More

In above code, i have saved the record in the first row only. I didnt understand that, after added one record, how do i added every new record on every new row line.

ajorourke · Jun 7th 2011

Re: new record on new row line?

I'm assuming you have are entering things into C4, c13, c22, c24, c26 and c27 to create a record and then want the records stored as lines of data?

If this is correct I suggest you have one sheet for data entry and a second sheet for the lines of data.

If you permanently have e4:j4 with the formula you put in the above code and then add the below code it will copy the line of data and create a new record on sheet 2
(where sheet 1 and sheet 2 please change to whatever you want to call the sheets)

Code

Sub AddRecord()
Application.ScreenUpdating = False
Sheets("Sheet2").Select
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Range("e4:J4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("A4").Select
Sheets("Sheet1").Select
Range("c4,c13,c22,c24,c26,c27").Select
Selection.ClearContents
Range("C4").Select
Application.ScreenUpdating = True
End Sub

Display More

PANKAJ UTEKAR · Jun 7th 2011

Re: new record on new row line?

I m suprising with your knowledge !
Thank You for quick reply.

i have using 2 sheets wherein-
---In 1st sheet, e4 to j4 got the value by clicking the cmdbuton.
---D 2nd sheet only contains values, that is absolutly right.

and when again i will click on cmdbuton then the other records goes to e5 to j5 and for every new record to e6....e7 and on.

For this i require code.

PANKAJ UTEKAR · Jun 8th 2011

Re: new record on new row line?

Can any one know d solution for following case?

AAE · Jun 10th 2011

Re: new record on new row line?

See if this does what you need.
It will copy the range of cells (C4, . . . C27) to next available row on sheet2, transposing the value from column to row format

Code

Option Explicit


Sub Copy_Cells()


    Dim nextrow As Long
    
    nextrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    Application.ScreenUpdating = False
    
    Range("C4,C13,C22,C24,C26,C27").Copy
    Sheet2.Range("A" & nextrow).PasteSpecial Transpose:=True
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True


End Sub

Display More

[COLOR="darkred"]@ajorourke:[/COLOR]

Re: post #2

All VBA code posted in the forum must be wrapped in code tags which you omitted. I've added the tags for you this time only. Be sure to use them in future posts.

[COLOR="navy"]How to use code tags[/COLOR]

[noparse]

Code

[/noparse]
[COLOR="navy"]your code goes between these tags[/COLOR]
[noparse]

[/noparse]

new record on new row line?

Participate now!

Share