Macro to select a variable cell range and copy it down a variable amount of times

I'm trying to create a work tool that will allow orders to be built in Excel line by line to be imported into NetSuite as a CSV file. Most of the order information is on one line from cells A2 to AB2 (with A1 to AB1 being headers), but the variability comes from the fact that the line items that are the physical items to be shipped as part of the order have to be stacked vertically in column Z, and linked together as one order by a unique ID in column A. Here is an excerpt from the NetSuite CSV Import Guide to show what I mean:

Attachment 631876

So the process would be to manually build one full order (e.g. date to ship, shipping location etc), stack the line items to be included in column Z and their quantities in column AA (in most cases each batch of shipments will be the same number of items just being sent to different places), and then have a macro select that one full order range (so for example, if my template order was sending 5 items, it would need to copy a range from A2:AB6, but it could also be 10 items and need to be A2:AB11), and copy it down a variable number of times. The amount of times that the one block order template (e.g. A2:AB6) needs to be repeated would be dictated by the number of sites that this type of order needs to be sent to, which would be listed in a different tab.

So the user will populate their basic order information in fields A2:AB2, stack their line items in Z2:Z#, paste their list of site references into the 'Site List' tab (e.g. site 100 in A1, site 101 in A2, site 102 in A3), click a macro button and the macro would copy the variable cell range (A2:AB#) dictated by the amount of rows in column Z (not including the header Z1), and then copy that block of rows and paste it down a number of times based on the number of rows with data in column A of the 'Site List' tab.

The last piece of the puzzle would then be to get those site references (100, 101, 102) into the cell in column U for each "order" (block of rows), because these site numbers are a necessary field for each order as this dictates the different final destinations for each order. So in the example with 5 line items and 3 sites, A1 from the 'Site List' tab (100) would need to be input to U2 on the 'Orders' tab, A2 (101) would need to map to U7, A3 (102) would need to map to U12 (separated by the amount of line items in Z2:Z#, 5 in this example).

I am capable of performing these functions myself just with copy/paste trickery, however I am creating this tool for a team that has no Excel expertise at all, and to get them using the tool I need to make it as easy to use as possible.

I've spent weeks staring at this problem, and every time I think I've found VBA code for a solution, it doesn't work for reasons that I can't figure out because of my lack of VBA expertise. Any help would be greatly appreciated.

So the user will fill out the basic order information in B2:T2, V2:Y2 + AB2. They will then stack line items in column Z, and this can be a variable quantity (could be two items, could be ten, depending on what the sites for that project need to receive). This is creating the basic order template for the batch of orders to be input (in a project the majority of sites will receive the same stuff).

The macro needs to perform the following steps:

1. Determine what one order is (A2:AB# - with # being the amount of line items in column Z not incl. header), and select and copy that "order". In the attached 'Web Help Example 1.xlsm' I've highlighted this "order" in a red box.
2. Copy this order template (in this example, A2:AB6) down an amount of times equivalent to the number of sites listed in the 'Site List' tab, area highlighted in a blue box. In this example it's 5 sites.
3. Apply an ascending unique identifier order # to column A.
4. Take the site numbers listed in the 'Site List' tab and apply those to the 'Site' "U" column in the 'Orders' tab, separated out by the Z number of line items so that they appear in line with the rest of the normal items for each order.

The attached examples should help to illustrate; Web Help Example 1.xlsm shows the starting point, and then Web Help Example 2.xlsm would show what the output should look like after the macro has ran.
In Web Help Example 2.xlsm, the info highlighted yellow will have been input via macro.

I think your interpretation is correct, and it worked for the small data-set in the example when I ran the macro, but I tried to stress-test the macro on a larger data-set and it didn't work as planned. I'm aware that this is a free service that you're providing in your own time and am very grateful for the help.

To stress-test, I put 150 site numbers onto the Site List tab (sequential from 100 to 249), added a few extra line items (8 total), and ran the macro. This wouldn't be an abnormal batch of orders. Attached, you can see what happened.

1. Rather than have a unique value for each "Order" (block of rows), after 1 and 2 it began repeating (so 3 was the unique identifier for the next four orders, and then 4 was the unique identifier for the next 8 orders, etc). It also overwrites the A column header with "1", which needs to stay as "Order #" as this is a field that gets mapped in NetSuite.
2. Rather than 150 "Orders" of 8 lines each (so should have been a total of 1,200 rows), it created 2,769 "Orders" and 21,857 rows. It seems like it needs to repeat several site numbers between each new number (so between 103 and 104's first appearances, 100, 101, 100, 102, 100, 101, 100 appear).
3. It didn't copy down the data in column AB. I think this may just be because AA2 is referenced in the code instead of AB2.

I think the macro is close to what I need and just needs a tweak so that larger data-sets don't send it into a loop.

This works much better, thank you. I'm sorry if I gave the impression that I wanted the order number in the U column; I do indeed want the site numbers from sh2 to appear in the U column. The way you've updated the code puts the order # in the U "Site" column. I tried just changing your code from:

sh1.Range("U2") = sh1.Range("A2").Value

to

sh1.Range("U2") = sh2.Range("A2").Value

But all this does is copy across the first site number (100) and then subsequently 2, 3, 4 etc from Order #, rather than 101, 102, 103 etc. Could you please update the code for that? It looks like we're (you're) very close to nailing it.

I would like to request one additional change, and if this is reaching the point where I need to pay for credits or something, I don't mind. Similarly to pulling the site numbers from sh2 column A to sh1 column U, I would also like to add an additional column to sh2 to copy across to sh1; "Ship-by". I forgot to account for the fact that, across a large batch of 100+ orders, the shipping date is likely to be different for each line. This should just be the same coding to transfer the site numbers across for sh2, except the data will be coming from sh2 column B and going into sh1 column L.

The attached example should illustrate what I'm trying to do (it shows how the data should look post-macro):

• Yellow column A - Auto-generate unique order numbers for each "Order" (batch of rows), starting with 1 and ascending (the code that you've written already works for this, so no change needed)

• Blue cells in column L - These should pull through the ship-by dates from the blue cells in sh2 column B (this would be a new line of code)

• Orange cells in column U - These should pull through the site numbers from the orange cells in sh2 column A (currently U populates with the order numbers from sh1 column A)

Thanks again for all your help.

Understood; as a newcomer I wasn't sure if there comes a point where someone like you has put an amount of time into a project like this where it needs to convert over to the commercial services forum. "as well as for users who need help with a specific problem but may not need to learn all there is to know about Excel" - this is my situation.

When trying to run the new code I get the following error: "Run-time error '438': Object doesn't support this property or method"

When debugging it highlights the new line of code "sh1.Cells(Rows.Count, 1).End(xlUp).Offset(, 10) = sh2(i + 1, 2).Value".

On the first try this looks like it works perfectly, thank you. I am on a two day training course now but will do a proper test on Thursday and I will report back if anything isn't working as it should be.

Thanks for all your time and effort on this.

The initial issue was satisfied; the latest code worked as intended. I started trying to beef the spreadsheet up, and thought that I would be able to tweak the code as I changed certain things necessary to make for a smooth user experience, however for some reason the copy down function of the code no longer works when I do this. All I did was add another tab, and added some more fields to the 'Site List' tab to be copied over to the 'Orders' tab.

If you look at the attached example, what I've tried to do to make the spreadsheet easy to use, is to have a first tab (Fields Same for Each Order) where the user enters the items that are static/consistent for all orders (and these will just be pulled through to the relevant cells in the Orders tab with formulas in the cells such as ='Fields Same for Each Order'!A$5.

Then the next step is to put the order-specific items (e.g. site number, shipping date, shipping service level) into the 'Site List' tab. Previously this tab was just 'Site' in column A and 'Ship-by' in column B, however when building the spreadsheet out I realized there needed to be more order-specific items filled out in the 'Site List' tab to copy across to the 'Orders' tab via the macro.

I adjusted the code as shown at the bottom of this post, building on your IF THEN section of code to pull data through from the 'Site List' tab, but now when I run the macro it no longer copies the 'Orders' (block of rows) down by a quantity dictated by the number of sites listed in 'Site List' column A. I haven't touched that part of the code so I'm not sure why it doesn't work now. Could you please take a look?

Hopefully my explanation makes sense; I've color-coded the 'Orders' sheet:

Yellow = New values that will be generated by the macro
Blue = Data that will pull across from the 'Fields Same For Each Order' tab without the macro being involved
Green = Data that should copy across from the 'Site List' tab when the macro is run.
Orange = Data that will be manually filled out in the 'Orders' tab, and will only be impacted by the macro in terms of being copied down as part of one 'Order'

Please Login or Register  to view this content.

(I'm aware that this isn't the tidiest nesting of the IF statements, however for some reason Excel wouldn't except them otherwise)