Copy Data From Multiple Columns in One Sheet to Another & Add Pre-Defined Value

censo

Board Regular
Joined
Dec 25, 2015
Messages
155
Office Version
  1. 2013
Platform
  1. Windows
Hello everyone,

On Sheet1 there's multiple columns with data of which I only need the data of 2 columns; G & H.

The Challenge:

1. The lines of data (rows) will vary (so cannot hard code it to the last line of data that's present at any given moment

2. The data to be copied over from Sheet 1 (columns G & H) cannot include the header information, so copying of cells begins at row 2

3. Column H, Sheet 1 needs to go to Column A, Sheet 2 (under the header - so beginning on row 2)
3a. Column G, Sheet 1 needs to go to Column B, Sheet 2 (under the header - so beginning on row 2)

4. Lastly, the exact string "USD" must be copied down, but only to the last row of data.

Can such a solution be crafted with functions or do we need to employ VBA code?

Thanks!

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">local_id</td><td style=";">vendor_url</td><td style=";">vendor_variant</td><td style=";">vendor_stock</td><td style=";">vendor_price</td><td style=";">vendor_shipping</td><td style=";">total</td><td style=";">reference</td><td style=";">compare_url</td><td style=";">compare_variant</td><td style=";">compare_stock</td><td style=";">compare_price</td><td style=";">compare_shipping</td><td style=";">profit_formula</td><td style=";">selling_formula</td><td style=";">reprice_store</td><td style=";">reprice_sku</td><td style=";">reprice_pause</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">555340</td><td style=";">http://www.walmart.com/ip/Nine-Stars-18.5-Gallon-Motion-Sensor-Recycle-Unit-and-Trash-Can-Stainless-Steel/15819203</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">86</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFFF00;;">86</td><td style="background-color: #9BC2E6;;">WAL-15819203</td><td style=";">#14535352829960</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AMAZON_US</td><td style=";">B0031M9H30</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">582037</td><td style=";">http://www.walmart.com/ip/Hamilton-Beach-33249-4-qt.-Stay-or-Go-Slow-Cooker/21898512</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">24.97</td><td style="text-align: right;;">4.97</td><td style="text-align: right;background-color: #FFFF00;;">29.94</td><td style="background-color: #9BC2E6;;">WAL-21898512</td><td style=";">#14535351981792</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AMAZON_US</td><td style=";">B005GZ2NDQ</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">582039</td><td style=";">http://www.homedepot.com/p/Stanley-3-in-1-Rolling-Workshop-STST18613/203367137</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">36.97</td><td style="text-align: right;;">5.99</td><td style="text-align: right;background-color: #FFFF00;;">42.96</td><td style="background-color: #9BC2E6;;">HDP-STST18613</td><td style=";">#14535351999089</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AMAZON_US</td><td style=";">B009CP4FAK</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">582041</td><td style=";">http://www.homedepot.com/p/BLACK-DECKER-20-Volt-Max-Lithium-Drill-and-Project-Kit-LDX120PK/204067339</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">69.99</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFFF00;;">69.99</td><td style="background-color: #9BC2E6;;">HDP-204067339-1</td><td style=";">#14535359391120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AMAZON_US</td><td style=";">B00C625KVE</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">582042</td><td style=";">http://www.walmart.com/ip/46529370</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">12.99</td><td style="text-align: right;;">4.97</td><td style="text-align: right;background-color: #FFFF00;;">17.96</td><td style="background-color: #9BC2E6;;">WAL-46529370</td><td style=";">#14535352251327</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AMAZON_US</td><td style=";">B00HJ6O4A0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">582043</td><td style=";">http://www.walmart.com/ip/Dorcy-41-4751-Dorcy-41-4751-180-Lumen-LED-Cyber-Light-Flashlight-Green/27434943</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">14.99</td><td style="text-align: right;;">4.97</td><td style="text-align: right;background-color: #FFFF00;;">19.96</td><td style="background-color: #9BC2E6;;">WAL-27434943</td><td style=";">#14535352145569</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AMAZON_US</td><td style=";">B008IHDTWS</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">582044</td><td style=";">http://www.walmart.com/ip/29384683</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">11.89</td><td style="text-align: right;background-color: #FFFF00;;">11.89</td><td style="background-color: #9BC2E6;;">WAL-29384683</td><td style=";">#1453535216737</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AMAZON_US</td><td style=";">B00DXRHQK4</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">582046</td><td style=";">http://www.walmart.com/ip/15409071</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">29.99</td><td style="text-align: right;;">4.97</td><td style="text-align: right;background-color: #FFFF00;;">34.96</td><td style="background-color: #9BC2E6;;">WAL-15409071</td><td style=";">#14535352023574</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AMAZON_US</td><td style=";">B00336TY0K</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;background-color: #FFD966;;">SKU</td><td style="font-weight: bold;background-color: #FFD966;;">COST</td><td style="font-weight: bold;text-align: right;background-color: #FFD966;;">CURRENCY</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #9BC2E6;;">WAL-15819203</td><td style="text-align: right;background-color: #FFFF00;;">86</td><td style=";">USD</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #9BC2E6;;">WAL-21898512</td><td style="text-align: right;background-color: #FFFF00;;">29.94</td><td style=";">USD</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #9BC2E6;;">HDP-STST18613</td><td style="text-align: right;background-color: #FFFF00;;">42.96</td><td style=";">USD</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #9BC2E6;;">HDP-204067339-1</td><td style="text-align: right;background-color: #FFFF00;;">69.99</td><td style=";">USD</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #9BC2E6;;">WAL-46529370</td><td style="text-align: right;background-color: #FFFF00;;">17.96</td><td style=";">USD</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #9BC2E6;;">WAL-27434943</td><td style="text-align: right;background-color: #FFFF00;;">19.96</td><td style=";">USD</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="background-color: #9BC2E6;;">WAL-29384683</td><td style="text-align: right;background-color: #FFFF00;;">11.89</td><td style=";">USD</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="background-color: #9BC2E6;;">WAL-15409071</td><td style="text-align: right;background-color: #FFFF00;;">34.96</td><td style=";">USD</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;font-style: italic;;">  ^^  (From Sheet 1)   ^^ </td><td style="font-weight: bold;text-align: center;font-style: italic;;"></td><td style="font-weight: bold;font-style: italic;;">^^ Auto-populated ^^</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />
 

censo

Board Regular
Joined
Dec 25, 2015
Messages
155
Office Version
  1. 2013
Platform
  1. Windows
Code:
Worksheets("Sheet2").Activate
e

Thanks for that update.

I ran into a couple of snafus. When I entered the additional code you supplied, I received a dialog box that says:

Run-time error '9':
Subscript out of range

Did I enter this last line the wrong way? The entire code looks like this:

Code:
Sub censo()
    With Worksheets("Sheet1").Range("A1").CurrentRegion
        .Offset(1).Columns("G").Copy Worksheets("AE UPLOAD").Range("A2")
        .Offset(1).Columns("H").Copy Worksheets("AE UPLOAD").Range("B2")
    End With


    With Worksheets("AE UPLOAD").Range("B2").CurrentRegion.Columns("B")
        .Offset(1, 1).Resize(.Rows.Count - 1, 1).Value = "USD"
    End With
    Worksheets("Sheet2").Activate
End Sub

Also, the finished result on Sheet2 is a template I use to upload to a website. For some reason that website doesn't like the file I save (File, Save As, and only saving that one particular sheet as a .csv). It's no big deal, I can simply copy and paste the results into another template and it works just fine.

To save a few extra steps though, what would the code look like to have the results generated on Sheet2 be all highlighted and in copy mode (with the marching ants)?

In other words, the VBA equivalent of pressing CTRL+A to highlight all cells that have data within the worksheet and then CTRL+C to copy.

Thank you!
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
I would say the error is because you don't have a worksheet named "Sheet2"or there's a space after the 2 or before it.

All the other stuff you asked about is a new thread, we're beyond the scope of your original request.
 
Last edited:

censo

Board Regular
Joined
Dec 25, 2015
Messages
155
Office Version
  1. 2013
Platform
  1. Windows
I would say the error is because you don't have a worksheet named "Sheet2"or there's a space after the 2 or before it.

If it were a snake, it would have jumped up and bit me - the answer was right underneath my nose. Haha, yes indeed I've renamed Sheet2 to 'AE UPLOAD'. Thanks for all your assistance.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,396
Messages
5,769,841
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top