VBA Help - Error when sinlge line is added.

[Trev0j]

Good day..

Need help - Newbie in VBA coding.

have below code:

Columns("Z:Z").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
'Selection.Copy
Range("x3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.FillDown
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Code is working fine when Multiple lines are added in a row.
hope to find working code when only single line is added.

thanks

 

[My Aswer Is This]

Would you please tell us in words what your wanting to do.

 

[Trev0j]

Would you please tell us in words what your wanting to do.

i'm trying to add data in my sheet which I have the formula "=TODAY()" on the last column to know the date I added the line as future reference.

No issues for Multiple lines as it will fill down the first line of whats been added. but for single line, it copy up to the first Row Range(Selection, Selection.End(xlUp)).Select. and fill down below which is Blank because the my first row is Blank.

sorry,,

 

[My Aswer Is This]

So when you:
add data to your sheet you want todays date entered in column Z is that correct?

How are you adding this data to your sheet?
Are you manually entering data? Or copying in data? Or is a formula entering the data or some other script entering the data.

 

[Trev0j]

So when you:
add data to your sheet you want todays date entered in column Z is that correct?

How are you adding this data to your sheet?
Are you manually entering data? Or copying in data? Or is a formula entering the data or some other script entering the data.

Here is my whole code -

Sub IT_PASTE()

'Application.ScreenUpdating = False
'Application.DisplayAlerts = False

If ActiveSheet.AutoFilterMode Then
Cells.AutoFilter
End If

Columns("B:B").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlUp).Select
'''''
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

''''''''''''''''''''''''
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'''''''''''''''''''''''''''''''''''''

Columns("Z:Z").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
'Selection.Copy
Range("x3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.FillDown
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Columns("B:B").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Rows("2:2").Select
Selection.AutoFilter

 

[My Aswer Is This]

I asked you what your trying to do.
And I thought you said enter todays date in column Z

There is no reason you should need to run all this much code to do what you want.

I asked how are you entering your data and you did not answer that question.

I can write about three lines of code that would enter todays date in column Z any time you enter any value in column A and this would be done automatically not requiring any script assigned to a button.
This would be done automatically when you enter any value in column A or B or X which ever you wanted.

 

[Trev0j]

I asked you what your trying to do.
And I thought you said enter todays date in column Z

There is no reason you should need to run all this much code to do what you want.

I asked how are you entering your data and you did not answer that question.

I can write about three lines of code that would enter todays date in column Z any time you enter any value in column A and this would be done automatically not requiring any script assigned to a button.
This would be done automatically when you enter any value in column A or B or X which ever you wanted.

so sorry for that. but what do you mean how i enter the data?

i need to copy data from other files and run the code above to paste it in the other file to add that at the bottom row and have the date automatically.
thanks for the help.

 

[Trev0j]

so sorry for that. but what do you mean how i enter the data?

i need to copy data from other files and run the code above to paste it in the other file to add that at the bottom row and have the date automatically.
thanks for the help.

What is the answer?

 

[Trev0j]

TODAY() - error for single row.

i am copying some data from other files and run some code to paste it on the other sheet and I want todays date to be entered in column Z.
have below code but no luck if only 1 row line is added.

Columns("Z:Z").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
'Selection.Copy
Range("x3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.FillDown
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Code is working fine when Multiple lines are added in a row.


Looking for other approach.

thanks for the help.

 

[MARK858]

Re: TODAY() - error for single row.

Maybe....

Sub xxxxx()
Range(Range("Z" & Rows.Count).End(xlUp).Offset(1), Range("Z" & Range("X" & Rows.Count).End(xlUp).Row)) = Date
End Sub