PDA

View Full Version : VBA Paste As Values



barim
01-17-2017, 09:22 AM
I have the following code that should concatenate values from column C using constant value of “X” from both sides, so numbers should look like X2001X and so on. Now, values are copied to the new worksheet called “Upload”. Columns that I need are copied to that sheet as well. Now, my problem is how to get rid of that CONCATENATE formula and paste it as values into “Upload” sheet. With these codes my formula is copied over and it messes up my data as you can see on worksheet “Upload”. I tried .PasteSpecial xlPasteValues and where ever I use it, it doesn’t work. I attached workbook. Thanks.



Sub MyAdd1 ()
'Declare variables
Dim lrow1 As Long
Dim c1 As Range

'Find the last row in column C
lrow1 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
'Loop through each cell in column C and concatenate values with “X” constant and print them in column A
For Each c1 In ActiveSheet.Range("C2:C" & lrow1)
Columns(1).Range("A2:A" & lrow1).FormulaR1C1 = "=CONCATENATE(""X"",RC[2],""X"")"
Next c1
' Adding and renaming Sheet “Upload”
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Upload"
' Into newly created sheet copy entire columns from Sheet (2)
Worksheets(1).Select
With Selection
Columns(1).Copy Destination:=Sheets(2).Columns(1)
Columns(4).Copy Destination:=Sheets(2).Columns(2)
Columns(6).Copy Destination:=Sheets(2).Columns(3)
End With

onlyadrafter
01-17-2017, 12:34 PM
Hello,

Does this solve it? Have just used the code you supplied, not the spreadsheet


Sub MyAdd1() 'Declare variables
Dim lrow1 As Long
Dim c1 As Range

'Find the last row in column C
lrow1 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
'Loop through each cell in column C and concatenate values with “X” constant and print them in column A
For Each c1 In ActiveSheet.Range("C2:C" & lrow1)
Columns(1).Range("A2:A" & lrow1).FormulaR1C1 = "=CONCATENATE(""X"",RC[2],""X"")"
Next c1
' Adding and renaming Sheet “Upload”
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Upload"
' Into newly created sheet copy entire columns from Sheet (2)
Worksheets(1).Select
With Selection
Columns(1).Copy
Sheets(2).Columns(1).PasteSpecial (xlPasteValues)
Columns(4).Copy
Sheets(2).Columns(2).PasteSpecial (xlPasteValues)
Columns(6).Copy
Sheets(2).Columns(3).PasteSpecial (xlPasteValues)
End With

mancubus
01-17-2017, 01:58 PM
don't miss dots (.) defore child objects, methods, properties in With-EndWith blocks...

barim
01-18-2017, 08:45 AM
It works. I thought that Destination has to be there. It is very simple solution after you replace Destination part. Mancubus, thank you so much for your help.