Variable length copy and paste

[Erwin65456]

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

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

 

[Fluff]

In what way isn't your code working?

 

[Erwin65456]

In what way isn't your code working?

Hi fluff,

I have managed to fix it today at work. I will post the resolution shortly.