how to automatically copy a few cells to a a table on another sheet?

[Wolthers]

Hi all,

I am new to Macros and VB (by new, I mean I have zero knowledge)

I hope someone could help me out on this puzzle, well, puzzle for me at least.

I have been looking around this forum, and saw many similar requests, but I could not figure out how to reuse the codes I found on some of the replies, some would just copy a bunch of rows and so on...

I make a report every week for some clients, and send via PDF, it's a report about prices.
I want to start keeping the data on the prices, so I can see via a chart the ups and downs of the prices.

attached is the Excel file.price repot test.xls

I only input data on the sheet Price_report_blaser, I want that cells B6, B7, B8, B19, and B27 will be copied to a new line inside the 'Table1' in price_report_statistics

forgot to mention, on the first column of the table, I would like to add today's date.

I tried to make a record macro, of course it didn't work :'(

Could anyone help me please? :D

[humdingaling]

Please Login or Register  to view this content.

try this code

[humdingaling]

Added extra couple of lines to fix your formatting

Please Login or Register  to view this content.

[Wolthers]

Thanks Humdingaling

It is copying all the data, including the date, but when I try and copy it again, it shows up on the same line, how can I add it on a new line below?

[AB33]

I think you have a table. The next row is 16. If you want to copy them in to row 4.

Please Login or Register  to view this content.

[Wolthers]

Thanks AB33, it works really well!!

If I want to add new cells to the database, the line I should add to the could would be

.Range("B30").Copy
ms.Range("F" & NR).PasteSpecial xlValues, Transpose:=True

right?

[AB33]

You are copying B30 in to column F, but NR is important. If you use the next empty row on the table

Please Login or Register  to view this content.

you get 16, so unless you want to post it to row 16, you have to hard coded it by specifying the next row no.
If you look at my code, you se NR=4.

[Wolthers]

it keeps copying to row 4, tomorrow, when I add new data, will it then create a new row using the variable 'NR = 4' plus the 'NR = NR +1' ?

[AB33]

I found the solution for the next empty row. See attached.

This finds the next empty row when ever you run the code.

[Wolthers]

Thank you very much AB33, it works like a charm :D

Now the last thing, is it possible not to create new rows for the same day? for instance, I change in the morning, but on the afternoon it's a different price already and I want to only show the last changed price instead of having multiple rows for the same day?

[humdingaling]

Please Login or Register  to view this content.

made a check to AB33 code that will cater for that