Variable length copy and paste

Erwin65456

New Member
Joined
Dec 19, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I don't know what I'm doing wrong here.

on sheet 1, variable length data will be provided in column A. only the 1st 5 characters are needed from column A.

for the sake of the vlookup herein, there is data in columns 1 through 7 on Sheet 2.

Eventually in the end I will be filtering the list by Department and saving PDFs based on the Department, but somehow that is simple for me, yet these basics are difficult.

I have tried a bunch of different ways (you will see that the method I tried in the first paragraph is not consistently applied throughout the other paragraphs, as I could not get it right).

at this point it is embarrassing that I don't understand how copy and paste works in VBA (I have looked on various websites as well).

the main point here is how to get the below as hard numbers only, as I will be adding filtering code to it later when I can get this right.

thanks

VBA Code:
Sub Whatever()
'this creates column A data. first 5 characters only, and pastes the values with no formulas
Range("B1").FormulaR1C1 = "=left(C[-1],5)"

Range("B1").AutoFill Destination:=Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)

Range(Selection, Selection.End(xlUp)).Select

Selection.Copy

Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False

Range(Selection, Selection.End(xlUp)).Select

Selection.Copy Range("B1")

Columns("A:B").Delete

'Range("B1").Paste 'column A (Code)




'this looks up a value from sheet 1 in column G based on sheet 2 column A values and pastes the values with no formula
Range("B1").FormulaR1C1 = "=VLOOKUP(C[-1],'Sheet1'!C[-1]:C[5],7,FALSE)"

Range("B1").AutoFill Destination:=Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)

Range("B1").Selection.End(xlDown).Select

Selection.Copy

Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False

Columns("B").Delete 'column B (Position Code)


'this takes a value from a different workbook in column B based on column A values and copies just the values with no formulas
Range("C1").FormulaR1C1 = _

"=VLOOKUP(LEFT(C[-1],9),'C:\Users\YourPath\DTH\[blahblah.xlsx]Sheet1'!C1:C3,2,FALSE)"

Range("C1").AutoFill Destination:=Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)

Range("C1").Selection.End(xlDown).Select

Selection.Copy

Range("D1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False

Columns("C").Delete 'column C (Title)


'same as the above except column C this time
Range("D1").FormulaR1C1 = _

"=VLOOKUP(LEFT(C[-2],9),'C:\Users\YourPath\DTH\[blahblah.xlsx]Sheet1'!C1:C3,3,FALSE)"

Range("D1").AutoFill Destination:=Range("D1:C" & Range("A" & Rows.Count).End(xlUp).Row)

Range("D1").Selection.End(xlDown).Select

Selection.Copy

Range("E1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False

Columns("D").Delete 'column D (Department)
End Sub
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
In what way isn't your code working?
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,342
Messages
5,624,116
Members
416,012
Latest member
rockermom59

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