Ricky,

1. Here's a simple function to test for file exists

Function FileExists(fn As String) As Boolean
If Dir(fn) < "" Then FileExists = True
End Function

2. Is this okay?

Sub PopualteSheets()
Dim cLastRow As Long

cLastRow = Worksheets("MasterFile").Cells(Rows.Count, "A").End(xlUp).Row

Worksheets("MasterFile").Range("A1:H" & cLastRow).Copy _
Destination:=Worksheets("Case"). _
Cells(Rows.Count, "A").End(xlUp)(2)

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ricky" wrote in message
om...
I have an Excel application that does a web query once a day into a
Worksheet called MasterFile that's pasted in rows. Each row represents
unique data that has a corresponding labeled worksheet based on what's
in the first cell(A1 through A?). Currently, I paste up to 20 rows of
data into the MasterFile worksheet and populate 20 worksheets from the
MasterFile sheet checking each worksheet for the next empty row. The
anmount of rows is potentially going to increase in the near future,
that means I'll have to manually add more sheets to match the amount
of rows I query in.

First Excel ??: How can I check if a worksheet already exists and if
not create one automatically?

Second Excel ??: How do I streamline the following code:

Sub PopualteSheets()
Worksheets("MasterFile").Range("A1:H1").Copy _
Destination:=Worksheets("Case"). _
Cells(Rows.Count, "A").End(xlUp)(2)

Worksheets("MasterFile").Range("A2:H2").Copy _
Destination:=Worksheets("Field"). _
Cells(Rows.Count, "A").End(xlUp)(2)

Worksheets("MasterFile").Range("A3:H3).Copy _
Destination:=Worksheets("Product"). _
Cells(Rows.Count, "A").End(xlUp)(2)

Worksheets("MasterFile").Range("A4:H4").Copy _
Destination:=Worksheets("Investment"). _
Cells(Rows.Count, "A").End(xlUp)(2)

'The above code is repeated 20 times once for each row in the
MasterFile.
'It copies a row and pastes it in it's corresponding worksheet into
the next empty row.

End Sub

Can the above subroutine be coded into an array or some type of
looping solution so I don't have to keep adding extra code when a new
row of data appears in the MasterFile worksheet??

Thanks,Rich