excel batch processing

A

anwarbham

i have a script that gets an agents name from a column (B:B) in a work
sheet(front)(saves the name as dim), then searches a sheet called
"data" auto filters and copys the data for this agents name to a new
sheet creates active links renames the sheet to the agents name. then
goes to another sheet called "front" sees if the agent exsists if he
does it skips inserting his name to this column else it adds his name
to this column. and moves on to the next name in the (front) sheet and
re does the process for the next agent.

what im stuck on is that if the worksheet for that agent exsists i just
want the code to paste the data in that sheet and not to create a new
sheet and then continue the loop to the next agents name

can any one help

Dim R As Range, MyInput As String
Sub testnew()

' Start of loop

With ActiveWorkbook.Sheets("front")
For Each R In .Range("B2", .Range("B2").End(xlDown))
MyInput = R.Value

' Adds new member and creates active link preserving the formatting
data sheet

ActiveWorkbook.Sheets("data").Activate
Application.ScreenUpdating = True
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollRow = 1
Columns("R:R").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=MyInput
Intersect(Range("A:AB"), ActiveSheet.UsedRange).Select

' This filter the spread sheet by asking the user for an agents
name
' and then selects only the cells with data in it

Selection.Copy

Sheets.Add.Name = "test"
ActiveSheet.Paste Link:=True
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

' This pastes the data in a new sheet and calls it test

ActiveWindow.ScrollColumn = 9
Range("R2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("test").Select
Sheets("test").Name = MyInput

ActiveSheet.Range("b30").Formula = "=COUNTIF(L:L,Front!E1)" ' open
calls
ActiveSheet.Range("b31").Formula = "=COUNTIF(L:L,Front!F1)" '
resolved calls
ActiveSheet.Range("a30").FormulaR1C1 = "OPEN"
ActiveSheet.Range("a31").FormulaR1C1 = "RESOLVED"

' this asks the user to rename the new sheet to the users name

Application.CutCopyMode = False
ActiveWorkbook.Sheets("front").Activate

' selects the front sheet deletes any duplicte entrys

Columns("B:B").Select
Columns("B:B").AdvancedFilter Action:=xlFilterInPlace,
unique:=True
Selection.Copy
Sheets.Add.Name = "dump"
Range("B1").Select
ActiveSheet.Paste
Sheets("Front").Select
Application.CutCopyMode = False

' paste unique entrysa back to orignal location

ActiveSheet.ShowAllData
Selection.ClearContents
Sheets("dump").Select
Columns("B:B").Select
Selection.Copy
Sheets("Front").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("dump").Select
Application.DisplayAlerts = False
Worksheets("dump").Delete
Application.DisplayAlerts = True
Range("a1").Select

' Goes back to data sheet gets rid of filter

Sheets("data").Select
Selection.AutoFilter
Sheets("front").Select
Range("b1").Select


Next R
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top