Macro Help

[r1983]

Hi

I hope someone can help with my small query. I have various tabs in my Excel sheet and I would like to write a macro which will go in each tab and insert "TEST" in Column A after the last raw. In column B copy the text from the cell above and in column C, insert "TEST2"

Is this possible? Ta x

 

[JackDanIce]

Try:

Sub SuperWaterCooler ()
 
Dim i as Long
 
Application.ScreenUpdating = False
 
For i = 1 to Worksheets.Count
   With Sheets(i)
      .Range("A" & Rows.Count).End(xlUp).Row + 1 = "TEST"
   End With
Next i
 
Sheets(1).Select
 
Application.ScreenUpdating = True
 
End Sub

 

[r1983]

Hi JackDanIce,

Thanks for such prompt response. I think your response was posted whilst I was updating my initial post. I am sure the code you supplied will work great but I need to add into Column B (copy the values from cell above) and Column C ("Test2")

Thanks a bunch

R

Try:

Sub SuperWaterCooler ()
 
Dim i as Long
 
Application.ScreenUpdating = False
 
For i = 1 to Worksheets.Count
   With Sheets(i)
      .Range("A" & Rows.Count).End(xlUp).Row + 1 = "TEST"
   End With
Next i
 
Sheets(1).Select
 
Application.ScreenUpdating = True
 
End Sub

 

[JackDanIce]

No worries, but you're not being specific, where in Column B and to copy the text immediately from the row above? Same, where in Column C?

 

[r1983]

No worries, but you're not being specific, where in Column B and to copy the text immediately from the row above? Same, where in Column C?

Apologies again

I have a master sheet with the sales data. My Macros splits them by manager in each tab.

Example -

Sheet 1 = Mark Smith
Sheet 2 = Gemma Green
Sheet 3 = Adriana Roberts

Once my split macro runs, at present I go in and manually add the data as below (in more than 50 Tabs )after the last raw (containing data).

I add in Column A (after the last raw) = "Overall Result".
In Column B (after the last raw which contains the data) = Copy the Last Raw above. ie-> If last raw B41 = "Sunderland" and it is the last raw, I go in B42 and copy B41
In Column C (after the last raw) = "Result"

I hope this makes sense. I admit I am no expert at VB Codes but have tried to explain, hoping it is possible to achieve it. Thanks for your valuable time.

Thanks,
R

 

[JackDanIce]

That's ok, just makes it easier to help solve problems when more detail/better clarity is given

Try:

Sub SuperWaterCooler ()
 
Dim i as Long
 
Application.ScreenUpdating = False
 
For i = 1 to Worksheets.Count
   With Sheets(i)
      .Range("A" & Rows.Count).End(xlUp).Row + 1 = "Overall Result"
      .Range("B" & Rows.Count).End(xlUp).Row + 1.Value = _
      .Range("B" & Rows.Count).End(xlUp).Row.Value
      .Range("C" & Rows.Count).End(xlUp).Row + 1 = "Result"
   End With
Next i
 
Sheets(1).Select
 
Application.ScreenUpdating = True
 
End Sub

 

[r1983]

I am sure its me being daft.
First - When putting code in -

Range("B" & Rows.Count).End(xlUp).Row + 1.Value = _
.Range("B" & Rows.Count).End(xlUp).Row.Value

That line shows up as Compile Error - Expected End statement.

Without those two lines when I run it, it shows Error 13 = "Type Mismatch"

Am I missing a trick somewhere?

 

[JackDanIce]

No, it was me messing up!

Try:

Sub SuperWaterCooler()
 
Dim i As Long, j As Long
 
Application.ScreenUpdating = False
 
For i = 1 To Worksheets.Count
    With Sheets(i)
        j = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & j) = "Overall Result"
        .Range("B" & j) = Range("B" & j - 1)
        .Range("C" & i) = "Result"
   End With
Next i
 
Sheets(1).Select
 
Application.ScreenUpdating = True
 
End Sub

 

[r1983]

Okay. I have tried this but have error - 1004 - You can not edit data cells for fields in the row, column or page area.

Will sending you the spreadsheet itself help?

Ta x

 

[JackDanIce]

Sure send to me, private message me your email address