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

[chicalaeti]

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.

Please Login or Register  to view this content.

[Jakobshavn]

You need to re-calculate LastRow for each worksheet separately.

[chicalaeti]

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!

Please Login or Register  to view this content.

[Jakobshavn]

Please Login or Register  to view this content.

[chicalaeti]

Yes! Thank you! Thank you!

[venkatpvc]

I know you got that what you wanted, to make short your code you can use this.
every time you run this code it will pop up the InputBox and then just you have to enter the value that you want insert in that sheet.

Please Login or Register  to view this content.

[Sa DQ]

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 

[chicalaeti]

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

[chicalaeti]

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!