VBA Concatenation Help

A

avd88

Board Regular
Joined
Jan 18, 2016
Messages
112
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) :

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:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
where is your code bugging out? My guess is it is to do with the number of times you're using copy and paste. My suggestion would be to pick the row with the longest set of data and use that to define your last row for the other columns, and then copy and past the entire range into the new sheet.
I'm not a VBA expert but I ran into a similar issue and it was caused by how my code was structured rather than what it was trying to accomplish if that makes sense.
 
Upvote 0
Hello RMKBrit,

Thanks for your response, actually all of that is working.
The code opens a new workbook, renames the spreadsheet, and pastes all the columns I need.
It bugs out at the
Code: 
Dim nextrow As Integer

[COLOR=#333333]nextrow = WorksheetFunction.CountA(ActiveSheet.Range("B2:B"))[/COLOR]

part (Object defined error).

Not sure why as I've used a similar dynamic range before and it worked fine, I think it has to do with the fact that this is a new workbook but not sure where the error is or if the concatenation code will work.
 
Upvote 0
Honestly I think the best way is to just insert the formula via vba into the first cell of the range and then have it copy and pasted to the end of the range, let me know if it doesn't make sense, I have some code I can post up when I am in the office
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
VBA - Increment range and loop until blank Cell
Replies
3
Views
126
dreid1011
dreid1011
O
  • Solved
VBA AutoFilter
2
Replies
19
Views
418
OX_2005
O
M
  • Question
VBA only copy data from one sheet that isn't already on another sheet
Replies
0
Views
489
mik1996
M
E
  • Question
VBA Copy and paste rows but keep formulas in place
Replies
2
Views
260
Darmlucht
D
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
185
bh24524
B

Forum statistics

Threads
1,214,943
Messages
6,122,380
Members
449,080
Latest member
Armadillos

Share this page

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
Back
Top