Macros - button function

[sutton_rich1]

I have data in one sheet in columns and i need to add this to a separate sheet in rows. when this has been added then it needs to be cleared from the original sheet. when new data is added to the first sheet then it needs to be added to the next line in the second sheet.

this needs to be done via button on sheet 1.

 

[TKB]

Try this:

Private Sub Button1_Click()

'Assuming the new information is in cell A1 that needs to be copied over

Range("A1").Select

Selection.Copy

'Go to the new sheet (the sheet your storing the information on)

Sheets("Sheet Name").Select

'Assuming you want the information to be pasted into column A of this sheet.

Range("A1").Select

'Tell the macro to find the next cell that does not have any information in it

Do Until ActiveCell.Select = ""

ActiveCell.Offset(1,0).Select

Loop

'Past the information into the empty cell (which will be at the bottom of the list)

Activesheet.[COLOR=#333333]PasteSpecial xlPasteValues[/COLOR]

'Now lets get rid of what was on the original sheet

Sheets("original sheet").Select

Range("A1").Select

ActiveCell.Value = ""

End Sub

Theres probably a shorter macro/formula to do this, but this should do what your looking for.

 

[sutton_rich1]

hi,

i tried the code but the loop did not stop and it kept going on and on.

i developed this code

Private Sub CommandButton1_Click()
Dim a As Integer
Sheet3.Calculate
a = Sheet4.Cells(1, 56) + 2
Sheet4.Cells(a, 1) = a - 1
'Sheet4.Cells(a, 2) = Sheet3.Cells(2, 3)
'Sheet4.Cells(a, 3) = Sheet3.Cells(3, 3)
'Sheet4.Cells(a, 4) = Sheet3.Cells(4, 3)
'Sheet4.Cells(a, 5) = Sheet3.Cells(5, 3)
'Sheet4.Cells(a, 6) = Sheet3.Cells(2, 8)
'Sheet4.Cells(a, 7) = Sheet3.Cells(3, 8)
'Sheet4.Cells(a, 8) = Sheet3.Cells(4, 8)
'Sheet4.Cells(a, 9) = Sheet3.Cells(5, 8)
Sheet4.Cells(a, 9) = Sheet3.Cells(7, 4)
Sheet4.Cells(a, 10) = Sheet3.Cells(8, 4)
Sheet4.Cells(a, 11) = Sheet3.Cells(9, 4)
Sheet4.Cells(a, 12) = Sheet3.Cells(10, 4)
Sheet4.Cells(a, 13) = Sheet3.Cells(11, 4)
Sheet4.Cells(a, 14) = Sheet3.Cells(12, 4)
Sheet4.Cells(a, 15) = Sheet3.Cells(13, 4)
Sheet4.Cells(a, 16) = Sheet3.Cells(14, 4)
Sheet4.Cells(a, 17) = Sheet3.Cells(15, 4)
Sheet4.Cells(a, 18) = Sheet3.Cells(16, 4)
Sheet4.Cells(a, 19) = Sheet3.Cells(17, 4)
Sheet4.Cells(a, 20) = Sheet3.Cells(18, 4)
Sheet4.Cells(a, 21) = Sheet3.Cells(19, 4)
Sheet4.Cells(a, 22) = Sheet3.Cells(20, 4)
Sheet4.Cells(a, 23) = Sheet3.Cells(21, 4)
Sheet4.Cells(a, 24) = Sheet3.Cells(22, 4)
Sheet4.Cells(a, 25) = Sheet3.Cells(23, 4)
Sheet4.Cells(a, 26) = Sheet3.Cells(24, 4)
Sheet4.Cells(a, 27) = Sheet3.Cells(25, 4)
Sheet4.Cells(a, 28) = Sheet3.Cells(26, 4)
Sheet4.Cells(a, 29) = Sheet3.Cells(27, 4)
Sheet4.Cells(a, 30) = Sheet3.Cells(7, 9)
Sheet4.Cells(a, 31) = Sheet3.Cells(8, 9)
Sheet4.Cells(a, 32) = Sheet3.Cells(9, 9)
Sheet4.Cells(a, 33) = Sheet3.Cells(10, 9)
Sheet4.Cells(a, 34) = Sheet3.Cells(11, 9)
Sheet4.Cells(a, 35) = Sheet3.Cells(12, 9)
Sheet4.Cells(a, 36) = Sheet3.Cells(13, 9)
Sheet4.Cells(a, 37) = Sheet3.Cells(14, 9)
Sheet4.Cells(a, 38) = Sheet3.Cells(15, 9)
Sheet4.Cells(a, 39) = Sheet3.Cells(16, 9)
Sheet4.Cells(a, 40) = Sheet3.Cells(17, 9)
Sheet4.Cells(a, 41) = Sheet3.Cells(18, 9)
Sheet4.Cells(a, 42) = Sheet3.Cells(19, 9)
Sheet4.Cells(a, 43) = Sheet3.Cells(20, 9)
Sheet4.Cells(a, 44) = Sheet3.Cells(21, 9)
Sheet4.Cells(a, 45) = Sheet3.Cells(22, 9)
Sheet4.Cells(a, 46) = Sheet3.Cells(23, 9)
Sheet4.Cells(a, 47) = Sheet3.Cells(24, 9)
Sheet4.Cells(a, 48) = Sheet3.Cells(25, 9)
Sheet4.Cells(a, 49) = Sheet3.Cells(26, 9)
Sheet4.Cells(a, 50) = Sheet3.Cells(27, 9)
Sheet4.Cells(a, 51) = Sheet3.Cells(28, 9) 'tm no
Sheet4.Cells(a, 52) = Sheet3.Cells(29, 4)
Sheet4.Cells(a, 53) = Sheet3.Cells(30, 4)
'Sheet4.Cells(a, 55) = Sheet3.Cells(29, 4)
'-----
'Sheet3.Cells(2, 3) = vbNullString
'Sheet3.Cells(3, 3) = vbNullString
'Sheet3.Cells(4, 3) = vbNullString
'Sheet3.Cells(5, 3) = vbNullString
'Sheet3.Cells(2, 8) = vbNullString
'Sheet3.Cells(3, 8) = vbNullString
'Sheet3.Cells(4, 8) = vbNullString
'Sheet3.Cells(5, 8) = vbNullString
Sheet3.Cells(7, 4) = vbNullString
Sheet3.Cells(8, 4) = vbNullString
Sheet3.Cells(9, 4) = vbNullString
Sheet3.Cells(10, 4) = vbNullString
Sheet3.Cells(11, 4) = vbNullString
Sheet3.Cells(12, 4) = vbNullString
Sheet3.Cells(13, 4) = vbNullString
Sheet3.Cells(14, 4) = vbNullString
Sheet3.Cells(15, 4) = vbNullString
Sheet3.Cells(16, 4) = vbNullString
Sheet3.Cells(17, 4) = vbNullString
Sheet3.Cells(18, 4) = vbNullString
Sheet3.Cells(19, 4) = vbNullString
Sheet3.Cells(20, 4) = vbNullString
Sheet3.Cells(21, 4) = vbNullString
Sheet3.Cells(22, 4) = vbNullString
Sheet3.Cells(23, 4) = vbNullString
Sheet3.Cells(24, 4) = vbNullString
Sheet3.Cells(25, 4) = vbNullString
Sheet3.Cells(26, 4) = vbNullString
Sheet3.Cells(27, 4) = vbNullString
Sheet3.Cells(7, 9) = vbNullString
Sheet3.Cells(8, 9) = vbNullString
Sheet3.Cells(9, 9) = vbNullString
Sheet3.Cells(10, 9) = vbNullString
Sheet3.Cells(11, 9) = vbNullString
Sheet3.Cells(12, 9) = vbNullString
Sheet3.Cells(13, 9) = vbNullString
Sheet3.Cells(14, 9) = vbNullString
Sheet3.Cells(15, 9) = vbNullString
Sheet3.Cells(16, 9) = vbNullString
Sheet3.Cells(17, 9) = vbNullString
Sheet3.Cells(18, 9) = vbNullString
Sheet3.Cells(19, 9) = vbNullString
Sheet3.Cells(20, 9) = vbNullString
Sheet3.Cells(21, 9) = vbNullString
Sheet3.Cells(22, 9) = vbNullString
Sheet3.Cells(23, 9) = vbNullString
Sheet3.Cells(24, 9) = vbNullString
Sheet3.Cells(25, 9) = vbNullString
Sheet3.Cells(26, 9) = vbNullString
Sheet3.Cells(27, 9) = vbNullString
Sheet3.Cells(28, 9) = vbNullString
Sheet3.Cells(29, 4) = vbNullString
Sheet3.Cells(30, 4) = vbNullString
End Sub

but when i set try to add the next data from the source sheet it over writes the previously imported data rather than going to the next line.