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]

try this on a copy of your file.
you will need to add a sheet named "Sheet2" (without the quotes)

Sub do_it()


Dim rs As Worksheet
Set rs = Worksheets("Sheet2")
rs.Cells.ClearContents 'remove all data from sheet 2

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row 'llop thur all itesm

store = Cells(r, "A")
sup = Cells(r, "B")
Item = Cells(r, "C")

lr = rs.Cells(Rows.Count, "F").End(xlUp).Row  'last ro w in sheet 2 with data

For q = 1 To 7 'loop thru all quantities
rs.Cells(lr + q, "F") = store
rs.Cells(lr + q, "G") = sup
rs.Cells(lr + q, "J") = Item
rs.Cells(lr + q, "L") = Cells(r, 12 + q) ' quantity- starts at column m
rs.Cells(lr + q, "M") = Cells(r, 26 + q * 2) 'price - starte at column AB
Next q

Next r

End Sub

hth,

-Ross

 

[karl10220]

Sub do_it() Dim rs As Worksheet Set rs = Worksheets("Sheet2") rs.Cells.ClearContents 'remove all data from sheet 2 For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row 'llop thur all itesm store = Cells(r, "A") sup = Cells(r, "B") Item = Cells(r, "C") lr = rs.Cells(Rows.Count, "F").End(xlUp).Row 'last ro w in sheet 2 with data For q = 1 To 7 'loop thru all quantities rs.Cells(lr + q, "F") = store rs.Cells(lr + q, "G") = sup rs.Cells(lr + q, "J") = Item rs.Cells(lr + q, "L") = Cells(r, 12 + q) ' quantity- starts at column m rs.Cells(lr + q, "M") = Cells(r, 26 + q * 2) 'price - starte at column AB Next q Next r End Sub

Thank you! The error came up with 'Compile Error: Variable not defined' "For r = 2..." on line 4 is where it happens.

 

[Joe4]

Thank you! The error came up with 'Compile Error: Variable not defined' "For r = 2..." on line 4 is where it happens.

Sounds like you have the "Option Explicit" setting turned on (which is a good thing!).
This requires you to declare ALL of your variables in your code before using them (which is a "best practice"!).

You declared one variable here:

Dim rs As Worksheet

but have not declared your other variables (r, store, sup, Item, lr and q).

So you should declare all these other variables at the top of your code. Then that message will go away.
This is a great tool to help find typos and other coding issues, so I would recommend leaving it on.

 

[karl10220]

Thank you. I am not sure what method to declare them as. Can you Joe4 assist or rpaulson?

 

[rpaulson]

here is the entire code, I revamped it a little.

Option Explicit
Sub do_it()

Dim r As Integer, q As Integer, lr As Integer

With Worksheets("Sheet2")
.Cells.ClearContents 'remove all data from sheet 2


For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row 'llop thur all itesm

lr = .Cells(Rows.Count, "F").End(xlUp).Row  'last ro w in sheet 2 with data

For q = 1 To 7 'loop thru all quantities

.Cells(lr + q, "F") = Cells(r, "A") 'store
.Cells(lr + q, "G") = Cells(r, "B") 'supplier
.Cells(lr + q, "J") = Cells(r, "C") 'item
.Cells(lr + q, "L") = Cells(r, 12 + q) ' quantity- starts at column m
.Cells(lr + q, "M") = Cells(r, 26 + q * 2) 'price - starte at column AB

Next q

Next r

End With
End Sub

 

[Joe4]

Thank you. I am not sure what method to declare them as. Can you Joe4 assist or rpaulson?

It is simply "Dim" -ing each variable, like I already shown you did with the one variable already.

Dim rs As Worksheet

and

Dim r As Integer, q As Integer, lr As Integer

Basically, you are telling it what data types all your variables are before using them.

See here for a detailed explanation: Declaring variables (VBA)

 

[karl10220]

I'm sorry to be a pain. This is now returning a Compile Error: Syntax error and highlighting Sub do_it()

 

[Joe4]

There is nothing that is in that line of code that should be causing an error.

Can you post all the VBA code that you have in that particular module, exactly as you currently have it?

 

[karl10220]

I fixed the error I had in there! This works wonderfully! I have 2 'nice to have' things if they are possible. Beginning on the quantity cells, can I pull M, O, Q, S, U, W, Y (every other column) and price from AB, AD, AF, AH, AJ, AL, AN? I can modify the sheet if needed, but if that above works I won't have to!

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

Again, these are NICE TO HAVE, but I sincerely appreciate this!