R
ryguy7272
I’m trying to figure out a way to loop through a list (now the range is
A1:E10, but it will change) and copy/paste each unique set of numbers into
it’s own sheet. For instance, I want to put all 1s in ColumnA on Sheet named
‘1’, all 2s in ColumnA on sheet named ‘2’, etc. I have numbers 1-7 in my
range now (so I want to dynamically create 7 sheets), but this will change.
This is the code I am working with now:
Sub CopyNums()
Dim c As Range
Dim d As Range
Dim FirstAddress As String
Dim myFindString As String
Dim NewSht As Worksheet
myFindString = "1"
With ActiveSheet.Range("A:E")
Set c = .Find(myFindString, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
Set d = c
FirstAddress = c.Address
End If
Selection.Copy
Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> FirstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
'Add a worksheet
'Set NewSht = ActiveWorkbook.Worksheets.Add
'NewSht.Name = myFindString
Range("G1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Range("A1").Select
End With
d.Select
End Sub
That takes all the 1s and put them in G1 (of the same sheet, but this is not
what I want to do). So, it doesn't let me do what I want to do, and in fact,
it only works sometimes. Ugh! I guess the Union operator is getting screwed
up. Any suggestions as to how I can make this work?
Thanks,
Ryan---
A1:E10, but it will change) and copy/paste each unique set of numbers into
it’s own sheet. For instance, I want to put all 1s in ColumnA on Sheet named
‘1’, all 2s in ColumnA on sheet named ‘2’, etc. I have numbers 1-7 in my
range now (so I want to dynamically create 7 sheets), but this will change.
This is the code I am working with now:
Sub CopyNums()
Dim c As Range
Dim d As Range
Dim FirstAddress As String
Dim myFindString As String
Dim NewSht As Worksheet
myFindString = "1"
With ActiveSheet.Range("A:E")
Set c = .Find(myFindString, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
Set d = c
FirstAddress = c.Address
End If
Selection.Copy
Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> FirstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
'Add a worksheet
'Set NewSht = ActiveWorkbook.Worksheets.Add
'NewSht.Name = myFindString
Range("G1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Range("A1").Select
End With
d.Select
End Sub
That takes all the 1s and put them in G1 (of the same sheet, but this is not
what I want to do). So, it doesn't let me do what I want to do, and in fact,
it only works sometimes. Ugh! I guess the Union operator is getting screwed
up. Any suggestions as to how I can make this work?
Thanks,
Ryan---