Hello I have a code which copies and pastes several columns into a new workbook starting on column B.
I now want column A to be a concatenation of column B and column C, I am getting an object defined error with the code I'm trying to use, any help?
Here is my code (The concatenation part is at the end, I converted part of the code into notes just so I can test it quicker) :
Thanks!
I now want column A to be a concatenation of column B and column C, I am getting an object defined error with the code I'm trying to use, any help?
Here is my code (The concatenation part is at the end, I converted part of the code into notes just so I can test it quicker) :
Code:
Private Sub CustomerServiceButton_Click()
'Variables'
Dim wbI As Workbook, wbII As Workbook
Dim wsI As Worksheet, wsII As Worksheet
'Current Workbook variables'
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Intercias")
'Output workbook variables
Set wbII = Workbooks.Add
wbII.ActiveSheet.Name = "Analysis"
With wbII
Set wsII = wbII.Sheets("Analysis")
'WorkwsII.Range("B1:AZ20000").Delete Shift:=xlUp
'FIND LAST ROW '
copyLastrow = wsI.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
pasteLastrowB = wsII.Range("B" & Rows.Count).End(xlUp).Row
pasteLastrowC = wsII.Range("C" & Rows.Count).End(xlUp).Row
pasteLastrowD = wsII.Range("D" & Rows.Count).End(xlUp).Row
pasteLastrowE = wsII.Range("E" & Rows.Count).End(xlUp).Row
pasteLastrowF = wsII.Range("F" & Rows.Count).End(xlUp).Row
pasteLastrowG = wsII.Range("G" & Rows.Count).End(xlUp).Row
pasteLastrowH = wsII.Range("H" & Rows.Count).End(xlUp).Row
pasteLastrowI = wsII.Range("I" & Rows.Count).End(xlUp).Row
pasteLastrowJ = wsII.Range("J" & Rows.Count).End(xlUp).Row
pasteLastrowK = wsII.Range("K" & Rows.Count).End(xlUp).Row
pasteLastrowL = wsII.Range("L" & Rows.Count).End(xlUp).Row
pasteLastrowM = wsII.Range("M" & Rows.Count).End(xlUp).Row
pasteLastrowN = wsII.Range("N" & Rows.Count).End(xlUp).Row
pasteLastrowO = wsII.Range("O" & Rows.Count).End(xlUp).Row
pasteLastrowP = wsII.Range("P" & Rows.Count).End(xlUp).Row
pasteLastrowQ = wsII.Range("Q" & Rows.Count).End(xlUp).Row
pasteLastrowR = wsII.Range("R" & Rows.Count).End(xlUp).Row
pasteLastrowS = wsII.Range("S" & Rows.Count).End(xlUp).Row
pasteLastrowT = wsII.Range("T" & Rows.Count).End(xlUp).Row
pasteLastrowU = wsII.Range("U" & Rows.Count).End(xlUp).Row
pasteLastrowV = wsII.Range("V" & Rows.Count).End(xlUp).Row
pasteLastrowW = wsII.Range("W" & Rows.Count).End(xlUp).Row
pasteLastrowX = wsII.Range("X" & Rows.Count).End(xlUp).Row
pasteLastrowY = wsII.Range("Y" & Rows.Count).End(xlUp).Row
pasteLastrowZ = wsII.Range("Z" & Rows.Count).End(xlUp).Row
pasteLastrowAA = wsII.Range("AA" & Rows.Count).End(xlUp).Row
pasteLastrowAB = wsII.Range("AB" & Rows.Count).End(xlUp).Row
pasteLastrowAC = wsII.Range("AC" & Rows.Count).End(xlUp).Row
pasteLastrowAD = wsII.Range("AD" & Rows.Count).End(xlUp).Row
pasteLastrowAE = wsII.Range("AE" & Rows.Count).End(xlUp).Row
pasteLastrowAF = wsII.Range("AF" & Rows.Count).End(xlUp).Row
pasteLastrowAG = wsII.Range("AG" & Rows.Count).End(xlUp).Row
pasteLastrowAH = wsII.Range("AH" & Rows.Count).End(xlUp).Row
pasteLastrowAI = wsII.Range("AI" & Rows.Count).End(xlUp).Row
pasteLastrowAJ = wsII.Range("AJ" & Rows.Count).End(xlUp).Row
pasteLastrowAK = wsII.Range("AK" & Rows.Count).End(xlUp).Row
pasteLastrowAL = wsII.Range("AL" & Rows.Count).End(xlUp).Row
pasteLastrowAM = wsII.Range("AM" & Rows.Count).End(xlUp).Row
pasteLastrowAN = wsII.Range("AN" & Rows.Count).End(xlUp).Row
pasteLastrowAO = wsII.Range("AO" & Rows.Count).End(xlUp).Row
pasteLastrowAP = wsII.Range("AP" & Rows.Count).End(xlUp).Row
pasteLastrowAQ = wsII.Range("AQ" & Rows.Count).End(xlUp).Row
pasteLastrowAR = wsII.Range("AR" & Rows.Count).End(xlUp).Row
pasteLastrowAS = wsII.Range("AS" & Rows.Count).End(xlUp).Row
pasteLastrowAT = wsII.Range("AT" & Rows.Count).End(xlUp).Row
pasteLastrowAU = wsII.Range("AU" & Rows.Count).End(xlUp).Row
pasteLastrowAV = wsII.Range("AV" & Rows.Count).End(xlUp).Row
pasteLastrowAW = wsII.Range("AW" & Rows.Count).End(xlUp).Row
'COPY PASTE'
wsI.Range("Q3:Q" & copyLastrow).Copy
wsII.Range("B" & pasteLastrowB).PasteSpecial (xlPasteValues)
wsI.Range("C3:C" & copyLastrow).Copy
wsII.Range("C" & pasteLastrowC).PasteSpecial (xlPasteValues)
wsI.Range("H3:H" & copyLastrow).Copy
wsII.Range("D" & pasteLastrowD).PasteSpecial (xlPasteValues)
'wsI.Range("I3:I" & copyLastrow).Copy
'wsII.Range("E" & pasteLastrowE).PasteSpecial (xlPasteValues)
'wsI.Range("J3:J" & copyLastrow).Copy
'wsII.Range("F" & pasteLastrowF).PasteSpecial (xlPasteValues)
'wsI.Range("K3:K" & copyLastrow).Copy
'wsII.Range("G" & pasteLastrowG).PasteSpecial (xlPasteValues)
'wsI.Range("O3:O" & copyLastrow).Copy
'wsII.Range("H" & pasteLastrowH).PasteSpecial (xlPasteValues)
'wsI.Range("A3:A" & copyLastrow).Copy
'wsII.Range("I" & pasteLastrowI).PasteSpecial (xlPasteValues)
'wsI.Range("FT3:FT" & copyLastrow).Copy
'wsII.Range("J" & pasteLastrowJ).PasteSpecial (xlPasteValues)
'wsI.Range("FS3:FS" & copyLastrow).Copy
'wsII.Range("K" & pasteLastrowK).PasteSpecial (xlPasteValues)
'wsI.Range("NR3:NR" & copyLastrow).Copy
'wsII.Range("L" & pasteLastrowL).PasteSpecial (xlPasteValues)
'wsI.Range("NS3:NS" & copyLastrow).Copy
'wsII.Range("M" & pasteLastrowM).PasteSpecial (xlPasteValues)
'wsI.Range("D3:D" & copyLastrow).Copy
'wsII.Range("N" & pasteLastrowN).PasteSpecial (xlPasteValues)
'wsI.Range("L3:L" & copyLastrow).Copy
'wsII.Range("O" & pasteLastrowO).PasteSpecial (xlPasteValues)
'wsI.Range("M3:M" & copyLastrow).Copy
'wsII.Range("P" & pasteLastrowP).PasteSpecial (xlPasteValues)
'wsI.Range("N3:N" & copyLastrow).Copy
'wsII.Range("Q" & pasteLastrowQ).PasteSpecial (xlPasteValues)
'wsI.Range("EV3:EV" & copyLastrow).Copy
'wsII.Range("R" & pasteLastrowR).PasteSpecial (xlPasteValues)
'wsI.Range("EW3:EW" & copyLastrow).Copy
'wsII.Range("S" & pasteLastrowS).PasteSpecial (xlPasteValues)
'wsI.Range("EX3:EX" & copyLastrow).Copy
'wsII.Range("T" & pasteLastrowT).PasteSpecial (xlPasteValues)
'wsI.Range("FA3:FA" & copyLastrow).Copy
'wsII.Range("U" & pasteLastrowU).PasteSpecial (xlPasteValues)
'wsI.Range("ET3:ET" & copyLastrow).Copy
'wsII.Range("V" & pasteLastrowV).PasteSpecial (xlPasteValues)
'wsI.Range("EM3:EM" & copyLastrow).Copy
'wsII.Range("W" & pasteLastrowW).PasteSpecial (xlPasteValues)
'wsI.Range("EU3:EU" & copyLastrow).Copy
'wsII.Range("X" & pasteLastrowX).PasteSpecial (xlPasteValues)
'wsI.Range("EK3:EK" & copyLastrow).Copy
'wsII.Range("Y" & pasteLastrowY).PasteSpecial (xlPasteValues)
'wsI.Range("R3:R" & copyLastrow).Copy
'wsII.Range("Z" & pasteLastrowZ).PasteSpecial (xlPasteValues)
'wsI.Range("S3:S" & copyLastrow).Copy
'wsII.Range("AA" & pasteLastrowAA).PasteSpecial (xlPasteValues)
'wsI.Range("T3:T" & copyLastrow).Copy
'wsII.Range("AB" & pasteLastrowAB).PasteSpecial (xlPasteValues)
'wsI.Range("U3:U" & copyLastrow).Copy
'wsII.Range("AC" & pasteLastrowAC).PasteSpecial (xlPasteValues)
'wsI.Range("V3:V" & copyLastrow).Copy
'wsII.Range("AD" & pasteLastrowAD).PasteSpecial (xlPasteValues)
'wsI.Range("W3:W" & copyLastrow).Copy
'wsII.Range("AE" & pasteLastrowAE).PasteSpecial (xlPasteValues)
'wsI.Range("X3:X" & copyLastrow).Copy
'wsII.Range("AF" & pasteLastrowAF).PasteSpecial (xlPasteValues)
'wsI.Range("Y3:Y" & copyLastrow).Copy
'wsII.Range("AG" & pasteLastrowAG).PasteSpecial (xlPasteValues)
'wsI.Range("Z3:Z" & copyLastrow).Copy
'wsII.Range("AH" & pasteLastrowAH).PasteSpecial (xlPasteValues)
'wsI.Range("AA3:AA" & copyLastrow).Copy
'wsII.Range("AI" & pasteLastrowAI).PasteSpecial (xlPasteValues)
'wsI.Range("AB3:AB" & copyLastrow).Copy
'wsII.Range("AJ" & pasteLastrowAJ).PasteSpecial (xlPasteValues)
'wsI.Range("AC3:AC" & copyLastrow).Copy
'wsII.Range("AK" & pasteLastrowAK).PasteSpecial (xlPasteValues)
'wsI.Range("AP3:AP" & copyLastrow).Copy
'wsII.Range("AL" & pasteLastrowAL).PasteSpecial (xlPasteValues)
'wsI.Range("AQ3:AQ" & copyLastrow).Copy
'wsII.Range("AM" & pasteLastrowAM).PasteSpecial (xlPasteValues)
'wsI.Range("AR3:AR" & copyLastrow).Copy
'wsII.Range("AN" & pasteLastrowAN).PasteSpecial (xlPasteValues)
'wsI.Range("AS3:AS" & copyLastrow).Copy
'wsII.Range("AO" & pasteLastrowAO).PasteSpecial (xlPasteValues)
'wsI.Range("AT3:AT" & copyLastrow).Copy
'wsII.Range("AP" & pasteLastrowAP).PasteSpecial (xlPasteValues)
'wsI.Range("AU3:AU" & copyLastrow).Copy
'wsII.Range("AQ" & pasteLastrowAQ).PasteSpecial (xlPasteValues)
'wsI.Range("AV3:AV" & copyLastrow).Copy
'wsII.Range("AR" & pasteLastrowAR).PasteSpecial (xlPasteValues)
'wsI.Range("AW3:AW" & copyLastrow).Copy
'wsII.Range("AS" & pasteLastrowAS).PasteSpecial (xlPasteValues)
'wsI.Range("AX3:AX" & copyLastrow).Copy
'wsII.Range("AT" & pasteLastrowAT).PasteSpecial (xlPasteValues)
'wsI.Range("AY3:AY" & copyLastrow).Copy
'wsII.Range("AU" & pasteLastrowAU).PasteSpecial (xlPasteValues)
'wsI.Range("AZ3:AZ" & copyLastrow).Copy
'wsII.Range("AV" & pasteLastrowAV).PasteSpecial (xlPasteValues)
'wsI.Range("BA3:BA" & copyLastrow).Copy
'wsII.Range("AW" & pasteLastrowAW).PasteSpecial (xlPasteValues)
With wsII
Dim nextrow As Integer
nextrow = WorksheetFunction.CountA(ActiveSheet.Range("B2:B"))
wsII.Range("A2:A" & nextrow).Formula = "=CONCATENATE(B2,C2)"
End With
End With
End Sub
Thanks!
Last edited: