Vba copy paste

[kiwi101]

Hey guys

Im trying to copy some columns from one sheet and paste it onto another. I've come up with a code. Its supposed to copy items of 2 columns from the first worksheet which are "Profile" and "Email Address" and paste it onto the second worksheet on columns "ID"(Column 9 in Excel) and "Email Address"(Column 8 in Excel).
However it is only copying content of column "Profile" and pasting it twice on the second worksheet under "ID" and "Email Address" Can anyone see what I am doing wrong?
[/
Sub example()




Dim RoleWkb As Workbook, figWkb As Workbook, RoleWkst As Worksheet, figWkst As Worksheet


Set RoleWkb = Workbooks.Open("C:\Users\jjjj.xlsm")
Set figWkb = ThisWorkbook
Set RoleWkst = RoleWkb.Sheets("Profile")
Set figWkst = ConfigWkb.Worksheets("Information")


Dim cgroup As Range, cgroupstart As Range, cgroupend As Range
Dim ugroup As Range, ugroupstart As Range, ugroupend As Range
Dim name As Range



'***grabs procurement agents from workbook and copies
With figWkst
Set cgroup = .Columns(9).Find(What:="ID")
If Not cgroup Is Nothing Then
Set cgroupstart = cgroup.Offset(1)
Set cgroupend = Range(cgroupstart, cgroupstart.End(xlDown))
'if there are more than users
If WorksheetFunction.CountA(cgroupend) > 1 Then
cgroupend.Copy
'else (only one user)
Else: cgroupstart.Copy
End If
End If
End With

'pastes procurement agents to user role
With RoleWkst
Set ugroup = .Columns(1).Find(What:="User")
If Not ugroup Is Nothing Then
Set ugroupstart = ugroup.Offset(1, 0)
ugroupstart.PasteSpecial xlPasteValues
Set ugroupend = Range(ugroupstart, ugroupstart.End(xlDown))
If WorksheetFunction.CountA(ugroupend) > 2 Then
Else: Set ugroupend = Range(ugroupstart, ugroupstart.Offset(1))
End If
End If

End With


With figWkst
Set cgroup = .Columns(8).Find(What:="Email Address")
If Not cgroup Is Nothing Then
Set cgroupstart = cgroup.Offset(1, 1)
Set cgroupend = Range(cgroupstart, cgroupstart.End(xlDown))
If WorksheetFunction.CountA(cgroupend) > 1 Then
cgroupend.Copy
Else: cgroupstart.Copy
End If
End If
End With

'pastes cos USERID and e-mail
With RoleWkst
Set ugroup = .Columns(8).Find(What:="Email Address")
If Not ugroup Is Nothing Then
Set ugroupstart = ugroup.Offset(2, 2)
ugroupstart.PasteSpecial xlPasteValues
Set ugroupstart = ugroup.Offset(2, 4)
ugroupstart.PasteSpecial xlPasteValues
If WorksheetFunction.CountA(ugroupstart) > 1 Then
Set ugroupend = Range(ugroupstart, ugroupstart.End(xlDown))
Else: Set ugroupend = Range(ugroupstart, ugroupstart.Offset(1))
End If
End If
End With










End Sub
/]

 

[kiwi101]

I just ran this code in a test set up and it copied both ranges to the destination sheet putting one beneath the other with a one row separation

Ok so i think there a misunderstanding in what we both are trying to accomplish.
I want both the cells from sheet 1 to appear in a single cell in sheet 2.
So under the column Address: Paris France.
Paris came from Column 6 "city" in sheet 1
and France came from Column 7 "Country" in sheet 1. And they merge together under "Address" Column 2 of sheet 2 as Paris (space) France.

I believe what you are trying to do is paste City and let it appear under Address and then separate by an empty row and then paste the Country. so Paris (new row) Paris (new row) France(new row) France (new row)

 

[kiwi101]

This is why I tried to do the second paste with a space. But obviously its not working

.Cells(Rows.Count, 2).End(xlUp)(3).Space(1).PasteSpecial xlPasteValues

 

[JLGWhiz]

Ok so i think there a misunderstanding in what we both are trying to accomplish.
I want both the cells from sheet 1 to appear in a single cell in sheet 2.
So under the column Address: Paris France.
Paris came from Column 6 "city" in sheet 1
and France came from Column 7 "Country" in sheet 1. And they merge together under "Address" Column 2 of sheet 2 as Paris (space) France.

I believe what you are trying to do is paste City and let it appear under Address and then separate by an empty row and then paste the Country. so Paris (new row) Paris (new row) France(new row) France (new row)

Yes, that would eliminate some of the confusion, but not all of it. Are you trying to copy a range of values from one column in the source sheet to a single cell in the destination sheet and then copy a second range of values to the same cell in the destination sheet? It would be helpful if you can post a before and after scenario in a screen shot or a mock up. Click on the word "Attachments" at the bottom of my post to get links to different methods of posting images. I will wait for your response before attempting to offer any more code.

 

[kiwi101]

Hey
I've been working on attaching something though the Attachments link on your post but my computer keeps acting up. I'll keep trying but what you've said is essentially correct.

Are you trying to copy a range of values from one column in the source sheet to a single cell in the destination sheet and then copy a second range of values to the same cell in the destination sheet?

Yes but with a [Space] separating them. So instead of ParisFrance its Paris France

I'll keep trying for the attachment. But any guidance is helpful in the meantime.

 

[JLGWhiz]

Hey
I've been working on attaching something though the Attachments link on your post but my computer keeps acting up. I'll keep trying but what you've said is essentially correct.
[/COLOR]

Yes but with a [Space] separating them. So instead of ParisFrance its Paris France

I'll keep trying for the attachment. But any guidance is helpful in the meantime.

You would have to concatenate the values in the destination cell instead of trying to do a second paste. In other words, paste the first value in, then add the other values by concatenating the second and subsequent values. Exmple below.

Sub t()
Range("A1").Copy
Range("B1").PasteSpecial xlPasteValues
Range("B1") = Range("B1").Value & vbLf & vbLf & Range("A2").Value
End Sub

This would first put the value of A1 in cell B1, then it would add the value of cell A2 to cell B1 beneath the last value entered with a space between the entries. The vbLf generates a line feed each time it is used in the statement. I don't believe you can do more than one cell value at a time. That is, it would not work to try and do B1 & A2:A6.