"Bob Phillips" wrote in message ...
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


Bob, I don't think you quite understand what I'm asking. The first
thing I wanted to know is how to check if a sheet exists in a
workbook by matching each sheet label to the name in each row cell.

Example:

MasterFile Worksheet
A1="CASE"
A2="FIELD"
A3="Product"
and so on

Sheet(1) label=CASE
Sheet(2) label=FIELD
Sheet(3) label=Product
and so on

So, let's say yesterday I pull in 20 different rows of data. Each row
wuld be pasted in it's corresponding Sheet name based on the name in
the first cell of each row in the MasterFile. Example: cell A1 which
is CASE and has data in cells B1:H1 is pasted in sheet CASE row A1:H1.
The same goes for cell A2 which has FIELD in it, would be pasted in
sheet FIELD row A1:H1.

So, if I ever have more rows than sheets I want to automatically add
and label a new sheet for the new row in the MasterFile.


And as far as the second ?? I don't think it's going to do what I was
looking for. I pasted your solution below.

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



Shouldn't the line:

Destination:=Worksheets("Case"). _

be something like this:

Destination:=Worksheets("variable"). _

Your solution would still require me to repeat each code section for
the amount of sheets I have. The destination worksheet should be a
variable not hardcoded per each sheet.

I hope this better explanation helps.

Thanks,
Rich