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
Suzanne wrote:
> 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
|
Similar Threads
