VBA Macro: Copy range of data to another sheet

I need a bit more information on how you get the data to the sales page. I think some of your issues can be uncomplicated if the data (at least rows 1 through 31) were in an Excel table. Then you could run a "pointer" down the column. A table knows how big it is, so the pointer will know where to start and stop.

I'm still not all that clear what you want to copy from the Sale page to the Historico page. In some cases it looks like it is copying blank cells.

Also on the sales page, the formulas in column G could be simplified by doing a lookup. What you have seems to work fine. If I have to nest more than two if statements, I go for a lookup. However, that's my personal preference - either way works.

In the attached, I made a table on the Sale page. I had to hide the header since you have a dropdown value on row 17. Excel tables must have fixed headers. The real headers are on row 18 which is hidden. I also changed the dropdown for the units of measure from the hard coded that you had to read it from a lookup table. If you are using a list validation against a column in a table you can use the following syntax:

=INDIRECT("TableName[TableColumn]")

The validation will grow and shrink with the data in the table.

I also took the if statement out of column G and replaced it with: =IF(B19="","",VLOOKUP($G$17,Table_Units,2,FALSE)*[@Volume]) This looks up the value to multiply. It also means you do not have to recode anything should a new unit of measure come along. Just add the new unit to the table on the Lookups page.

With this setup I was able to run a pointer, cl, down the first column on Table_Sales. cl is a range. When used with For Each, it starts with the first cell in the range and moves on to the next cell in the range until it reaches the end. That's why I call it a pointer since it points to each cell in order.

This is where I am confused. it looks like you are copying static data, basically replicating the same row after row. I took a best guess as to what you wanted.

About the only thing to explain about the code is the use of offset. cl.Offset(0,5).Value means go zero rows down and 5 columns over from the cell where cl is pointing and get its value.

Please Login or Register  to view this content.

Hi Dflak, first of all, thank you very much for your help once again, really appreciate it, and merry Christmas!

Originally Posted by dflak

I need a bit more information on how you get the data to the sales page. I think some of your issues can be uncomplicated if the data (at least rows 1 through 31) were in an Excel table. Then you could run a "pointer" down the column. A table knows how big it is, so the pointer will know where to start and stop.

I'm still not all that clear what you want to copy from the Sale page to the Historico page. In some cases it looks like it is copying blank cells.

I will always manually input:

Commission: D9 (if buyer pays) and K9 (if seller pays)
Shipments: range(B18:B31)
Volume: range(E18:E31)

What I need to copy:
Contract nr.: range (I19:I32) Contract number will always be a unique number, it is based on last contract number +1, for now I manually input last contract number on N5, and have a basic formula on cell I18 that is N5+1, and the rest is just above cell +1. Maybe you can come with a smarter way than that, like getting the last value on the Historico table (called it tbl_historico now), column B instead of manually doing it?
Buyer: D5
Seller: I5
Quality: D14
Price: D15
Shipments: range(B19:B32)
Volume: range(E19:E32)
Commission S: D9
Commission B: I9

Originally Posted by dflak

Also on the sales page, the formulas in column G could be simplified by doing a lookup. What you have seems to work fine. If I have to nest more than two if statements, I go for a lookup. However, that's my personal preference - either way works.

In the attached, I made a table on the Sale page. I had to hide the header since you have a dropdown value on row 17. Excel tables must have fixed headers. The real headers are on row 18 which is hidden. I also changed the dropdown for the units of measure from the hard coded that you had to read it from a lookup table. If you are using a list validation against a column in a table you can use the following syntax:

=INDIRECT("TableName[TableColumn]")

The validation will grow and shrink with the data in the table.

I also took the if statement out of column G and replaced it with: =IF(B19="","",VLOOKUP($G$17,Table_Units,2,FALSE)*[@Volume]) This looks up the value to multiply. It also means you do not have to recode anything should a new unit of measure come along. Just add the new unit to the table on the Lookups page.

Thanks, that volume formula worked much better, I went ahead and also changed the validation list to the first column on lookups.

As of the rest of the VBA code, one thing I noticed is the clearTable, which we won’t need, I am doing this in order to save all the data of all the contracts that we made, just as a control method - I was not able to look yet into the rest of the VBA, but will do that when I can – have to get on with the christmass foods now

Originally Posted by dflak

With this setup I was able to run a pointer, cl, down the first column on Table_Sales. cl is a range. When used with For Each, it starts with the first cell in the range and moves on to the next cell in the range until it reaches the end. That's why I call it a pointer since it points to each cell in order.

This is where I am confused. it looks like you are copying static data, basically replicating the same row after row. I took a best guess as to what you wanted.

About the only thing to explain about the code is the use of offset. cl.Offset(0,5).Value means go zero rows down and 5 columns over from the cell where cl is pointing and get its value.

Please Login or Register  to view this content.

Wish you a merry Christmas DFlaq, thanks for the help once again.