Find last empty cell and input text

[mulholm]

Is there anyway to amend the below code to allow it to work in multiple worksheets.

Also can it be amended to work as soon as data is input the the sheet rather than the use of a command button.

Sub Macro4()


'Step 1: Declare Your Variables.
Dim LastBlankRow As Long
'Step 2: Capture the last used row number.
LastBlankRow = Cells(Rows.Count, 1).End(xlUp).Row
'Step 3: Select the next row down
Cells(LastBlankRow, 2).Select
'Step 4: Enter "Team Total" in active cell
ActiveCell.FormulaR1C1 = "Team Total"

End Sub

The code works perfectly when input in Sheet1 however when i put the exact same code in to sheet 2 with identical data it give me the following message: Run-time error '1004': Application-defined or object-defined error.

Also it worked when i assigned to a command button but what i want it to do is work automatically when i paste data to the sheet.

 

[James006]

Hello,

You can test the following

[COLOR=#333333]Sub Macro5()[/COLOR]
[COLOR=#333333]'Step 1: Declare Your Variables.[/COLOR]
[COLOR=#333333]Dim LastBlankRow As Long[/COLOR]
[COLOR=#333333]'Step 2: Capture the last used row number.[/COLOR]
[COLOR=#333333]LastBlankRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row[/COLOR]
[COLOR=#333333]'Step 3: Select the next row down[/COLOR]
[COLOR=#333333]ActiveSheet.Cells(LastBlankRow, 2).Select[/COLOR]
[COLOR=#333333]'Step 4: Enter "Team Total" in active cell[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = "Team Total"[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

Hope this will help

 

[mulholm]

that worked great

Any idea how to get it to work by pasting my data rather than making a command button?

<colgroup><col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <tbody> </tbody>

 

[James006]

Glad it is working fine ...

You need to determine which Event should trigger the addition of ' Team Total ' ... :wink:

 

[mulholm]

Basically what i want to do is to paste in data to the sheet and once the data is pasted it will automatically action the code.



I have something similar on another workbook which uses - Private Sub Worksheet_Change(ByVal Target As Range)



I'm not sure if what i'm looking to do can be done but any help would be good.

 

[James006]

How is your data pasted ...?

Is it a manual operation ... or is it a macro ...?

Quite obviously ...if it is macro ...this instruction can be inserted at the end of the macro ...

 

[mulholm]

Manual operation.

 

[James006]

Re,

You could test following event macro ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Application.Match("Team Total", Range("B:B"), 0) > 0 Then Exit Sub
'Step 1: Declare your Variable
Dim LastBlankRow As Long
'Step 2: Capture the last used row number + 1 to get first available blank row '''''''''
LastBlankRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
'Step 3: Input the Total Header in column B ''''''''''''''''''''''''''''''''''''''''''''
ActiveSheet.Cells(LastBlankRow, 2) = "Team Total"
End Sub

Hope this will help

 

[mulholm]

Didn't seem to work

I may just put it in a command button.