VBA Copy certain cells and columns to another sheet

[karl10220]

I am stumped with what to do with this situation. I am trying to take data from a row with multiple variables and place it in a column. For each bottle purchased, there is a new price point based on a different order volume. That is currently in a row and I'd like it to be transferred to a column, but I'd need various data points from the row to then be pasted on each row for the individual data point. You'll see in the image. But Item 123 from Store 456, has quantities that relate to specific pricing, so I need the new sheet in column/table format to show 123-456-qty1-price1; in row 2 123-456-qty2-price2; etc. 7 times for each 123-456 combination. There are 2000 rows, so doing this manually is not an option, but 2000 rows will spit out 14000 lines it appears in this new format. Thank you so much for your help!

Note: the yellow column headers represent a new sheet "Sheet 2" with the same columns referenced .

 

[rpaulson]

to pull M, O, Q, S, U, W, Y for the qty. change the following

from
.Cells(lr + q, "L") = Cells(r, 12 + q) ' quantity- starts at column m
to
.Cells(lr + q, "L") = Cells(r, 11 + q*2) ' quantity- starts at column m

the price is already set to AB, AD, AF, AH, AJ, AL, AN in the original code

 

[Joe4]

Lastly, can columns AB-AN be rounded to 4 decimal points maximum? Currently it spits out 8 or so.

You can either adjust the cell formatting from those columns to only show 4 decimals.
Or you can use the ROUND function to round those entries to 4 decimals (the ROUND function exists both as a Worksheet function and a VBA function).

 

[karl10220]

Thank you both! I can't get the rounding feature to work, but this is a minimal problem!

 

[Joe4]

Thank you both! I can't get the rounding feature to work, but this is a minimal problem!

ROUND is a function, not a feature.
But wouldn't just formatting the cell to show 4 decimals suffice?
See here for how to do both: Round a number to the decimal places I want - Microsoft Support.

 

[karl10220]

Another perfectionist question here, can we 'clear all contents' after Row 1 on Sheet 2?

As for the rounding, since these will get uploaded into another program can you help write the VBA for rounding price to 4 spots?

 

[rpaulson]

change
' .Cells.ClearContents 'remove all data from sheet 2
to
.Range("F2:M" & .[F1].End(xlDown).Row).ClearContents

and

.Cells(lr + q, "M") = Cells(r, 26 + q * 2)
to
.Cells(lr + q, "M") = Round(Cells(r, 26 + q * 2), 4) 'this will round to 4 places

 

[karl10220]

Thank you so much! I have now applied the macro to the larger data set (which I anticipate to spit out over 14,000 rows!) and it stopped after row 2886 (half way) and gave an error Run-time error '13': Type Mismatch in the command

.Cells(lr + q, "M") = Round(Cells(r, 26 + q * 2), 4) 'this will round to 4 places 'price - starte at column AB


Any ideas?

EDIT:
Fixed the error on my sheet. It found a 'N/A' cell. I'm sorry to alarm. This has been such a learning for me and I am so grateful!