Write a Macro

[New Student 2020]

Hi
I work with various worksheets for stock control in out store and as the column in each sheet is updated regularly downwards so I would like to be able to get the latest value from the column ( same in each sheet = G) copy and paste it in the main worksheet (Products List) against each product name (B2)... I have started the code to find the last cell with value in one sheet as follows:
Sub Last_Col ()
Range (“G100000”).End(xlUp). Select
But I do not know how to copy the value and paste it in B2!??
Also if I want to run the same macro for all my sheets how can I do it and copy each value to a different cell in sheet ProductsList ?
Thank you very much for your help
Franco

 

[DanteAmor]

I can't download the file, it sends me an error.
Did you share the file?

Forget it, I already downloaded the file.

 

[DanteAmor]

Try this:

Sub LastValue()
  Dim sh1 As Worksheet, sh As Worksheet
  Dim f As Range
  
  Set sh1 = Sheets("Product List")
  For Each sh In Sheets
    Set f = sh1.Range("A:A").Find(sh.Name, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      f.Offset(, 1).Value = sh.Range("G" & Rows.Count).End(3).Value
    Else
      Set f = sh1.Range("A:A").Find(sh.Name, , xlValues, xlPart, , , False)
      If Not f Is Nothing Then
        f.Offset(, 1).Value = sh.Range("G" & Rows.Count).End(3).Value
      End If
    End If
  Next
End Sub

 

[New Student 2020]

HI Dante it works......
thank you very much for your help
take care
Kind regards
Franco

 

[DanteAmor]

Im glad to help you, thanks for the feedback.