Altering Paste Data

[Suzanne]

I'm pulling data from one worksheet to another with:

Sub IMPORT()

Dim myrange, copyrange As Range
Sheets("IMPORT FROM").Select
Set myrange = Range("C2:C200")
For Each c In myrange
If c.Value <> "" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If

End If
Next
copyrange.Copy
Sheets("IMPORT TO").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues

End Sub

1. Columns C & D in 'IMPORT FROM' contain first name and last name.

Q: Can I combine this data when pasting names (which range anywhere from
1-200 names), i.e., combine first and last name in 'IMPORT TO'!A4:A200.

2. Columns E through AD in 'IMPORT FROM' contain three variables:
0-9%
10-50%
51-100%

Note: These variables will not necessarily appear in every row/column;
however, they will always be associated with a name.

Q: How can I convert the variable to a letter when pasting to 'IMPORT TO';
specifically:
0-9% = R (Rarely)
10-50% = S (Sometimes)
51-100% = F (Frequently)

BTW... reducing the size of the variable to a single digit is needed to fit
all the data onto a hardcopy report that is already tight on legal size paper.

Thank you

 

[Roger Govier]

Hi Suzanne

Try the following
Sub MoveData()
Dim wsF As Worksheet, wsT As Worksheet
Dim i As Long

Set wsF = ThisWorkbook.Sheets("Import From")
Set wsT = ThisWorkbook.Sheets("Import To")

For i = 2 To 200
If wsF.Cells(i, "C") <> "" Then
wsT.Cells(i, "A") = wsF.Cells(i, "C") & " " & wsF.Cells(i, "D")
wsF.Range(wsF.Cells(i, "E"), wsF.Cells(i, "AD")).Copy
wsT.Cells(i, "B")
End If
Next

With wsT.Range("A2:AD200")
.Replace What:="0-9%", Replacement:="R", SearchOrder:=xlByRows
.Replace What:="10-50%", Replacement:="S", SearchOrder:=xlByRows
.Replace What:="51-100%", Replacement:="F", SearchOrder:=xlByRows
End With

wsT.Columns("A:A").EntireColumn.AutoFit
wsT.Columns("B:AB").ColumnWidth = 1.5
End Sub

 

[Suzanne]

Thanks VERY much!!

Hi Suzanne

Try the following
Sub MoveData()
Dim wsF As Worksheet, wsT As Worksheet
Dim i As Long

Set wsF = ThisWorkbook.Sheets("Import From")
Set wsT = ThisWorkbook.Sheets("Import To")

For i = 2 To 200
If wsF.Cells(i, "C") <> "" Then
wsT.Cells(i, "A") = wsF.Cells(i, "C") & " " & wsF.Cells(i, "D")
wsF.Range(wsF.Cells(i, "E"), wsF.Cells(i, "AD")).Copy
wsT.Cells(i, "B")
End If
Next

With wsT.Range("A2:AD200")
.Replace What:="0-9%", Replacement:="R", SearchOrder:=xlByRows
.Replace What:="10-50%", Replacement:="S", SearchOrder:=xlByRows
.Replace What:="51-100%", Replacement:="F", SearchOrder:=xlByRows
End With

wsT.Columns("A:A").EntireColumn.AutoFit
wsT.Columns("B:AB").ColumnWidth = 1.5
End Sub

--
Regards
Roger Govier

.