This is my problem. I need to setup an array to look in 408 or so names and I don't want to have to write ("billy", "beth", ... ) etc - I just want it to scan array A3:A408 instead and unsure how to write the macro to do this. Any help would be appreciated. This is what I have so far:
Sub Echo_Monthly_CSR_June()
'
' Macro8 Macro
' Macro recorded 6/21/2008 by Kelly Simcik
'
'
Windows("Monthly Macro Insert.xls").Activate
Sheets("CSR Data").Select
Dim MyArray As Variant, x As Long, c As Range
'Assuming your Source, book = Monthly Macro Insert.xls and Sheet = Copy & Paste Echo
'Also assuming your Destination, book = Monthly Macro Insert.xls and sheets = CSR Data
MyArray = Array("A3:A408").Select
For x = 0 To 405
With Workbooks("Monthly Macro Insert.xls").Sheets("Copy & Paste Echo")
Set c = .Cells.Find(What:=MyArray(x), After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not c Is Nothing Then
c.Offset(0, 2).Copy
Workbooks("Monthly Macro Insert.xls").Sheets("CSR Data").Range("B" & x + 3).PasteSpecial xlPasteValues
If Not c Is Nothing Then
c.Offset(0, 5).Copy
Workbooks("Monthly Macro Insert.xls").Sheets("CSR Data").Range("C" & x + 3).PasteSpecial xlPasteValues
End If
End If
End With
Next x
Range("A1").Select
Windows("Weekly Macro Insert.xls").Activate
Sheets("Supervisor Echo Results").Select
Range("B2:V28").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Windows("Weekly Macro Insert.xls").Activate
Sheets("Macro").Select
Range("A1").Select
End Sub
Sub Echo_Monthly_CSR_June()
'
' Macro8 Macro
' Macro recorded 6/21/2008 by Kelly Simcik
'
'
Windows("Monthly Macro Insert.xls").Activate
Sheets("CSR Data").Select
Dim MyArray As Variant, x As Long, c As Range
'Assuming your Source, book = Monthly Macro Insert.xls and Sheet = Copy & Paste Echo
'Also assuming your Destination, book = Monthly Macro Insert.xls and sheets = CSR Data
MyArray = Array("A3:A408").Select
For x = 0 To 405
With Workbooks("Monthly Macro Insert.xls").Sheets("Copy & Paste Echo")
Set c = .Cells.Find(What:=MyArray(x), After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not c Is Nothing Then
c.Offset(0, 2).Copy
Workbooks("Monthly Macro Insert.xls").Sheets("CSR Data").Range("B" & x + 3).PasteSpecial xlPasteValues
If Not c Is Nothing Then
c.Offset(0, 5).Copy
Workbooks("Monthly Macro Insert.xls").Sheets("CSR Data").Range("C" & x + 3).PasteSpecial xlPasteValues
End If
End If
End With
Next x
Range("A1").Select
Windows("Weekly Macro Insert.xls").Activate
Sheets("Supervisor Echo Results").Select
Range("B2:V28").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Windows("Weekly Macro Insert.xls").Activate
Sheets("Macro").Select
Range("A1").Select
End Sub