Concatenate Formula VBA Help!

[MurraySMTH]

Hello all,

I am stuck with a VBA error when I am attempting to use the concatenate formula in sheet one to then paste in A2 of another sheet. (Also using Lastrow as Long)

Can anyone review, I am getting no errors, but my data is not pasting to the second sheet.

Sub Concatenate()

Dim String1 As String
Dim String2 As String
Dim String3 As String
Dim String4 As String
Dim String5 As String


Dim full_string As String
Dim Lastrow As Long
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Select

String1 = Range("AV2" & Lastrow).Select
String2 = Range("D2" & Lastrow).Select
String3 = Range("AW2" & Lastrow).Select
String4 = Range("AC2" & Lastrow).Select
String4 = Range("AX2" & Lastrow).Select

full_string = String1 & String2 & String3 & String4 & String5

Worksheets("A_Feedback_Upload_20200722").Select
Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

 

[Michael M]

Firstly, you haven't copied anything to paste to the other sheet
Selecting, is not necessary AND doesn't treat the range as copied
Second, You have 2 String4 lines.

 

[MurraySMTH]

Firstly, you haven't copied anything to paste to the other sheet
Selecting, is not necessary AND doesn't treat the range as copied
Second, You have 2 String4 lines.

Great tips. Thanks

 

[Rick Rothstein]

In addition to what Michael posted, you have a problem with your assignments to String1, String2, etc. I'll single this one out, but all 5 of your String# assignments have the same problem. Your code has this line of code...

String1 = Range("AV2" & Lastrow).Select

You cannot Select and assign from that selection in a single line of code, so lets remove the Select (as you do not have to select the cell to assign its value to a variable). This leaves...

String1 = Range("AV2" & Lastrow)

Now, if Lastrow gets assigned, let's say, 100... your statement concatenates that onto AV2 to give a reference to cell AV2100 which I am sure is not correct. My guess is that you probably do not want the 2 attached to the column letter designation. My guess is the line should be...

String1 = Range("AV" & Lastrow)

Remember, you have this problem with all the String# assignments.

 

[MurraySMTH]

In addition to what Michael posted, you have a problem with your assignments to String1, String2, etc. I'll single this one out, but all 5 of your String# assignments have the same problem. Your code has this line of code...

String1 = Range("AV2" & Lastrow).Select

You cannot Select and assign from that selection in a single line of code, so lets remove the Select (as you do not have to select the cell to assign its value to a variable). This leaves...

String1 = Range("AV2" & Lastrow)

Now, if Lastrow gets assigned, let's say, 100... your statement concatenates that onto AV2 to give a reference to cell AV2100 which I am sure is not correct. My guess is that you probably do not want the 2 attached to the column letter designation. My guess is the line should be...

String1 = Range("AV" & Lastrow)

Remember, you have this problem with all the String# assignments.

Rick, Thank you for spending your time and effort explaining this to me. This helped alot. Trying to learn VBA has been tough. Your advice, helps us beginners to continue this challenge.