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]

Hi and welcome to MrExcel.

Try this. Results in B2 and below

Sub LastValue()
  Dim sh1 As Worksheet, sh As Worksheet
  Set sh1 = Sheets("ProductsList")
  For Each sh In Sheets
    If sh.Name <> sh1.Name Then sh1.Range("B" & Rows.Count).End(3)(2).Value = sh.Range("G" & Rows.Count).End(3).Value
  Next
End Sub

 

[New Student 2020]

Sub LastValue() Dim sh1 As Worksheet, sh As Worksheet Set sh1 = Sheets("ProductsList") For Each sh In Sheets If sh.Name <> sh1.Name Then sh1.Range("B" & Rows.Count).End(3)(2).Value = sh.Range("G" & Rows.Count).End(3).Value Next End Sub

Hi Dante thank you for the reply....I have tried but it gives me a runtime error 9, subscript out of range.....Was I supposed to use this together with the last column value macro?? ... Anyway if you could help me with a macro to find first cell with value in the column (xlUp) and to copy this value in the worksheet "ProductsList" cell B2......then I can write it for each worksheet with separate destination cell as needed...Thank you very much.....Kind regards Franco

 

[DanteAmor]

This macro is to copy the last value from column G and paste it into column B.

When the error occurs, press the debug button, and tell me which line of the macro it stops at.
Check that in your file you have a sheet name like this: "ProductsList"
Check the name in the macro, maybe it takes space "Products List" modify the macro and run the macro again.

 

[New Student 2020]

Hi Dante it workd but when I add another value in column G or amend it it does not change it on the Product List column B ….

it all shows 78 for the whole column but for the non existing sheets it should show nothing....

 

[DanteAmor]

Or I don't understand what you need.

In my test. I have 3 sheets:

Product.xlsm
	A						G
1
2
3							3
4							4
5
sheet1

Product.xlsm
	A						G
1
2
3							3
4							4
5							5
6
sheet2

Product.xlsm
	A						G
1
2
3							3
4							4
5							5
6							88
sheet3

Results after run the macro:

Product.xlsm
	A	B
1
2		4
3		5
4		88
5
Products List

Test my file:

Product.xlsm

drive.google.com

 

[New Student 2020]

Sorry maybe I did not explain properly..... I have 134 sheets with different names (I think that maybe that is the problem as when I created all the worksheets from the list with another macro they are in a different order chosen by the system maybe...… the names on the list do not match as item 1 = sheet1.....?? what do you think??), which I manually update in column G ( like your example, but I need the numbers to be updated in the same cell ) .... but if I change the number in column G and rerun the macro the result is copied in a different cell down the column B instead of updating B2 or B3 etc which matches the product on the list.....example I have entered 8000 in one worksheet and it is copied down the column B in product List in a cell in row 270....instead of sheet 4.... Also do I have to rerun the macro every time or is there a way to run it automatically when I save or maybe I can create a button on each sheet??!! thank you and appreciate your help.....

 

[DanteAmor]

You can generate a small example with the sheet "products list" (current), three sheets with data (don't put the 134 sheets, 3 sheets is enough). Finally the sheet "products list_2" with the expected result.
Upload your file to the cloud.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[New Student 2020]

Hi this is the link, hope the example is clear.... thank you.... kind regards Franco

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[New Student 2020]

EXAMPLE copy.xlsx

Shared with Dropbox

www.dropbox.com