Macro to fill column with text - How to make it stop at the last used row

Hello Forum,

I have an excel document with a few dozen sheets. In each sheet column B is empty and I need to fill that column (starting from the third row –B3) all the way down to the last used row of the sheet with a specific text (a product name).
Each sheet has a different text that needs to be filled in.

I am new to VBA but with a lot of research and ´forum-hunting´ I pieced together a macro.
The Macro references column A to establish in which row it should stop inputting text in column B.

My problem is the following:
The macro works great on the first sheet. (sh1 ¨AD MOSTAZA Y MIEL¨).
BUT in all the rest of the sheets it keeps inputting the text far past the last row used!

Can anyone help me fix it please?

P.S.: I only posted the Macro with 5 sheets as an example. The Macro I need to use is pretty much the same just for more sheets.

Sub InputProductName()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet
Dim sh5 As Worksheet

Set sh1 = ActiveWorkbook.Sheets("AD MOSTAZA Y MIEL")
Set sh2 = ActiveWorkbook.Sheets("AD SOYA Y LIMON")
Set sh3 = ActiveWorkbook.Sheets("AD ACHIOTE")
Set sh4 = ActiveWorkbook.Sheets("AD CESAR")
Set sh5 = ActiveWorkbook.Sheets("AD CASA")
LastRow = Range("A1:A" & Range("A1").End(xlDown).Row).Rows.Count

sh1.Range("B3").Value = "AD MOSTAZA Y MIEL"
sh1.Range("B3" & ":B" & LastRow).FillDown

sh2.Range("B3").Value = "AD SOYA Y LIMON"
Sh2.Range("B3" & ":B" & LastRow).FillDown

sh3.Range("B3").Value = "AD ACHIOTE"
sh3.Range("B3" & ":B" & LastRow).FillDown

sh4.Range("B3").Value = "AD CESAR"
sh4.Range("B3" & ":B" & LastRow).FillDown

sh5.Range("B3").Value = "AD CASA"
sh5.Range("B3" & ":B" & LastRow).FillDown

End Sub

Thanks for such a quick reply Jakobshavn!

I tried inserting the LastRow for each worksheet (pls see below) as well and it did not work
I am getting the same problem as before...

I also tried putting sh1.LastRow = ... , sh2.LastRow = ... etc and this gives me an error message.

How would you input the LastRow-line?


Thanks your help so far!

Sub InputProductName()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet
Dim sh5 As Worksheet

Set sh1 = ActiveWorkbook.Sheets("AD MOSTAZA Y MIEL")
Set sh2 = ActiveWorkbook.Sheets("AD SOYA Y LIMON")
Set sh3 = ActiveWorkbook.Sheets("AD ACHIOTE")
Set sh4 = ActiveWorkbook.Sheets("AD CESAR")
Set sh5 = ActiveWorkbook.Sheets("AD CASA")

sh1.Range("B3").Value = "AD MOSTAZA Y MIEL"
LastRow = Range("A1:A" & Range("A1").End(xlDown).Row).Rows.Count
sh1.Range("B3" & ":B" & LastRow).FillDown

sh2.Range("B3").Value = "AD SOYA Y LIMON"
LastRow = Range("A1:A" & Range("A1").End(xlDown).Row).Rows.Count
sh2.Range("B3" & ":B" & LastRow).FillDown

sh3.Range("B3").Value = "AD ACHIOTE"
LastRow = Range("A1:A" & Range("A1").End(xlDown).Row).Rows.Count
sh3.Range("B3" & ":B" & LastRow).FillDown

sh4.Range("B3").Value = "AD CESAR"
LastRow = Range("A1:A" & Range("A1").End(xlDown).Row).Rows.Count
sh4.Range("B3" & ":B" & LastRow).FillDown

sh5.Range("B3").Value = "AD CASA"
LastRow = Range("A1:A" & Range("A1").End(xlDown).Row).Rows.Count
sh5.Range("B3" & ":B" & LastRow).FillDown

End Sub

Yes! Thank you! Thank you!

Venkatpvc - Thanks for that! I may not use it for this file but it is definitly useful for some other macros I am trying to write

PHP Code: 

Sub myTest()
    Dim Sh As Worksheet
        
    For Each Sh In ThisWorkbook.WorkSheets
       If Left(Sh.Name,2)=  "AD" Then          
          Sh.Range([b1], [B1].End(xlDown)).Value = Sh.Name
       End If
    Next Sh  
End Sub 


SA DQ - I love the idea of having a shorter Macro like you wrote and have it input the ´text´ according to the sheet name.

However I am having some issues with it:
- It only inputs the info on the first sheet and then it comes up with an error message (error 1004 - Application defined or object defined error)
- The product names are inputted much past the last used row (column B is filled all the way to row 10.000+ and I only have about 100 rows of data)

Could you help me? I would really appreciate it!