Go to next cell if full

[albertmb]

Hi everyone,

I need help if possible:

In cell 'A1' I would like to enter data
I would like to transfer this data to a different cell lets say its cell
'D1' buy 'Cut & Paste'.

Is it possible to have a macro to identify that cell 'D1' contains data so
next entry will be pasted in cell 'D2' and next in cell 'D3' and so on.

Thank you for your kind support.

Albert

 

[מיכ×ל (מיקי) ×בידן]

Try the Event-Macro in the sheets level:
===========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
LR = Cells(Rows.Count, 4).End(xlUp).Row
If IsEmpty(Cells(1, 4)) Then
Target.Cut Cells(LR, 4)
Else
Target.Copy Cells(LR, 4).Offset(1)
End If
Application.EnableEvents = True
End Sub
=======
Micky

 

[albertmb]

Hi Mike,

Worked to perfection, thank you for your kind help.

Can you be so kind to help me in another detail? Can the Macro be fixed to
have the destination cell in another sheet?

 

[מיכ×ל (מיקי) ×בידן]

U R welcome.
=================================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
LR = Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row
If IsEmpty(Sheets("Sheet2").Cells(1, 4)) Then
Target.Cut Sheets("Sheet2").Cells(LR, 4)
Else
Target.Cut Sheets("Sheet2").Cells(LR, 4).Offset(1)
End If
Application.EnableEvents = True
End If
End Sub
============
Micky
--
והמשך/×™, × ×, ×œ×§×¨×•× ×ת השורה הב××”:
***********
×× ×ª×’×•×‘×ª×™ עזרה לחץ/×™, × ×, על <כן> בפס ×”×ופקי התחתון!
***********
מיכ×ל ×בידן
מנהל ×¤×•×¨×•× "×ופיס" ב"תפוז"
[Microsoft" Most Valuable Professional [MVP"

 

[albertmb]

Hi Mike

Worked to perfection once again

Thank you ever so much for your kind help

Best regards
Albert

U R welcome.
=================================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
LR = Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row
If IsEmpty(Sheets("Sheet2").Cells(1, 4)) Then
Target.Cut Sheets("Sheet2").Cells(LR, 4)
Else
Target.Cut Sheets("Sheet2").Cells(LR, 4).Offset(1)
End If
Application.EnableEvents = True
End If
End Sub
============
Micky
--
והמשך/×™, × ×, ×œ×§×¨×•× ×ת השורה הב××”:
***********
×× ×ª×’×•×‘×ª×™ עזרה לחץ/×™, × ×, על <כן> בפס ×”×ופקי התחתון!
***********
מיכ×ל ×בידן
מנהל ×¤×•×¨×•× "×ופיס" ב"תפוז"
[Microsoft" Most Valuable Professional [MVP"

Hi Mike,

Worked to perfection, thank you for your kind help.

Can you be so kind to help me in another detail? Can the Macro be fixed to
have the destination cell in another sheet?

 

[albertmb]

Hi Mike, its me again, Albert.

Sorry to bother you again, can this macro be applied to a range of cells as
the 'Target Adress?

What I would like is that the data will be transfered to 'Sheet2' after the
complition of the last cell in the range of the 'Target Address'

Thank You

Best Regards
Albert

U R welcome.
=================================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
LR = Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row
If IsEmpty(Sheets("Sheet2").Cells(1, 4)) Then
Target.Cut Sheets("Sheet2").Cells(LR, 4)
Else
Target.Cut Sheets("Sheet2").Cells(LR, 4).Offset(1)
End If
Application.EnableEvents = True
End If
End Sub
============
Micky
--
והמשך/×™, × ×, ×œ×§×¨×•× ×ת השורה הב××”:
***********
×× ×ª×’×•×‘×ª×™ עזרה לחץ/×™, × ×, על <כן> בפס ×”×ופקי התחתון!
***********
מיכ×ל ×בידן
מנהל ×¤×•×¨×•× "×ופיס" ב"תפוז"
[Microsoft" Most Valuable Professional [MVP"

Hi Mike,

Worked to perfection, thank you for your kind help.

Can you be so kind to help me in another detail? Can the Macro be fixed to
have the destination cell in another sheet?