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

censo

Board Regular
Joined
Dec 25, 2015
Messages
110
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 />
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Webbarr

Board Regular
Joined
Jun 26, 2015
Messages
88
Hi Censo,

Code:
Sub copyPasteData()

Dim copySheet As Worksheet, pasteSheet As Worksheet
Dim lastRow As Long, startRow As Long


Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")


lastRow = WorksheetFunction.CountA(copySheet.Range("G:G"))


copySheet.Range("G2:H" & lastRow).Copy


startRow = WorksheetFunction.CountA(pasteSheet.Range("A:A"))


If startRow = 1 Then
    startRow = 2
Else
    startRow = startRow + 1
End If


pasteSheet.Range("A" & startRow).PasteSpecial xlPasteValues


lastRow = WorksheetFunction.CountA(pasteSheet.Range("A:A"))


pasteSheet.Range("C" & startRow & ":" & "C" & lastRow).Value = "USD"


End Sub

The sheet names in quotes "Sheet1" etc.. need to be changed if you rename the sheets. I hope this is useful, please let me know!
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Here's another option, it assumes you have the headers in place on both sheets.
Code:
Sub censo()
    With Worksheets("Sheet1").Range("A1").CurrentRegion
        .Offset(1).Columns("H").Copy Worksheets("Sheet2").Range("A2")
        .Offset(1).Columns("G").Copy Worksheets("Sheet2").Range("B2")
    End With

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

censo

Board Regular
Joined
Dec 25, 2015
Messages
110
This is great info guys - thanks. Favor to ask though - I have no experience how to use or implement said code. Please provide a primer?
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642

ADVERTISEMENT

1) Press Alt + F11

2) In the new window that pops up choose the Insert menu and from the drop down choose Module.

3) Copy and paste the code into the white window that came up when you chose module.

4) Press Alt + q to get back to Excel.

5) If you have Excel 2007 or later save the file as a type .xlsm macro enabled workbook.

Make sure you enable macros when you are prompted.

Go into the list of macros, for me I have 2010 and I go to the developer tab and choose macros then I pick it from the list and choose run.

The name of the macro will be the part immediately after the word Sub in the code.

For my code the macro is named censo.
 
Last edited:

censo

Board Regular
Joined
Dec 25, 2015
Messages
110
1) Press Alt + F11

2) In the new window that pops up choose the Insert menu and from the drop down choose Module.

3) Copy and paste the code into the white window that came up when you chose module.

4) Press Alt + q to get back to Excel.

5) If you have Excel 2007 or later save the file as a type .xlsm macro enabled workbook.

Make sure you enable macros when you are prompted.

Go into the list of macros, for me I have 2010 and I go to the developer tab and choose macros then I pick it from the list and choose run.

The name of the macro will be the part immediately after the word Sub in the code.

For my code the macro is named censo.


skywriter, your solution works like a charm. Thank you kindly.

Perhaps you can assist me tweak the results a little. After the macro is ran, is it possible to switch view to (or make active) the sheet the data has been copied to (Sheet2)? If so, what additional code would need to be entered?

For clarification, whether I have 10 rows or 10,000 rows, your code will include everything down to the last row of data right?
 

censo

Board Regular
Joined
Dec 25, 2015
Messages
110

ADVERTISEMENT

Hi Censo,

Code:
Sub copyPasteData()

Dim copySheet As Worksheet, pasteSheet As Worksheet
Dim lastRow As Long, startRow As Long


Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")


lastRow = WorksheetFunction.CountA(copySheet.Range("G:G"))


copySheet.Range("G2:H" & lastRow).Copy


startRow = WorksheetFunction.CountA(pasteSheet.Range("A:A"))


If startRow = 1 Then
    startRow = 2
Else
    startRow = startRow + 1
End If


pasteSheet.Range("A" & startRow).PasteSpecial xlPasteValues


lastRow = WorksheetFunction.CountA(pasteSheet.Range("A:A"))


pasteSheet.Range("C" & startRow & ":" & "C" & lastRow).Value = "USD"


End Sub

The sheet names in quotes "Sheet1" etc.. need to be changed if you rename the sheets. I hope this is useful, please let me know!

Thanks for your reply, Webbarr
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
As long as you have data in A1 and there's no completely blank rows in your data between the first and last row you will be okay.

In the examples I provided, the first example is okay because there's an item in row 4, but the second example would be a problem.

Blank columns would not be an issue as long as there are headers in row 1.

Excel Workbook
ABC
1HeaderHeaderHeader
2testtesttest
3testtesttest
4test
5testtesttest
6testtesttest
Sheet1


Excel Workbook
ABC
1HeaderHeaderHeader
2testtesttest
3testtesttest
4
5testtesttest
6testtesttest
Sheet1
 

censo

Board Regular
Joined
Dec 25, 2015
Messages
110
As long as you have data in A1 and there's no completely blank rows in your data between the first and last row you will be okay.

In the examples I provided, the first example is okay because there's an item in row 4, but the second example would be a problem.

Blank columns would not be an issue as long as there are headers in row 1.

Ok, thanks for clarifying. Were you able to figure out how to make Sheet2 active in the original code you provided?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,715
Members
412,613
Latest member
EFRATA
Top