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

[censo]

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!

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	local_id	vendor_url	vendor_variant	vendor_stock	vendor_price	vendor_shipping	total	reference	compare_url	compare_variant	compare_stock	compare_price	compare_shipping	profit_formula	selling_formula	reprice_store	reprice_sku	reprice_pause
2	555340	http://www.walmart.com/ip/Nine-Stars-18.5-Gallon-Motion-Sensor-Recycle-Unit-and-Trash-Can-Stainless-Steel/15819203		1	86	0	86	WAL-15819203	#14535352829960			0	0			AMAZON_US	B0031M9H30	0
3	582037	http://www.walmart.com/ip/Hamilton-Beach-33249-4-qt.-Stay-or-Go-Slow-Cooker/21898512		1	24.97	4.97	29.94	WAL-21898512	#14535351981792			0	0			AMAZON_US	B005GZ2NDQ	0
4	582039	http://www.homedepot.com/p/Stanley-3-in-1-Rolling-Workshop-STST18613/203367137		1	36.97	5.99	42.96	HDP-STST18613	#14535351999089			0	0			AMAZON_US	B009CP4FAK	0
5	582041	http://www.homedepot.com/p/BLACK-DECKER-20-Volt-Max-Lithium-Drill-and-Project-Kit-LDX120PK/204067339		1	69.99	0	69.99	HDP-204067339-1	#14535359391120			0	0			AMAZON_US	B00C625KVE	0
6	582042	http://www.walmart.com/ip/46529370		1	12.99	4.97	17.96	WAL-46529370	#14535352251327			0	0			AMAZON_US	B00HJ6O4A0	0
7	582043	http://www.walmart.com/ip/Dorcy-41-4751-Dorcy-41-4751-180-Lumen-LED-Cyber-Light-Flashlight-Green/27434943		1	14.99	4.97	19.96	WAL-27434943	#14535352145569			0	0			AMAZON_US	B008IHDTWS	0
8	582044	http://www.walmart.com/ip/29384683		0	0	11.89	11.89	WAL-29384683	#1453535216737			0	0			AMAZON_US	B00DXRHQK4	0
9	582046	http://www.walmart.com/ip/15409071		1	29.99	4.97	34.96	WAL-15409071	#14535352023574			0	0			AMAZON_US	B00336TY0K	0
Sheet1

Excel 2012
	A	B	C
1	SKU	COST	CURRENCY
2	WAL-15819203	86	USD
3	WAL-21898512	29.94	USD
4	HDP-STST18613	42.96	USD
5	HDP-204067339-1	69.99	USD
6	WAL-46529370	17.96	USD
7	WAL-27434943	19.96	USD
8	WAL-29384683	11.89	USD
9	WAL-15409071	34.96	USD
10	^^ (From Sheet 1) ^^		^^ Auto-populated ^^
11
Sheet2

 

[censo]

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:

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!

 

[skywriter]

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.

 

[censo]

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.

 

[skywriter]

My pleasure, thanks for the feedback.