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

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 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

A post by Chip Pearson that I've saved:

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = Len(WB.Worksheets(SheetName).Name) 0
End Function

You can then call this function in code as follows:

If WorksheetExists("Sheet123") = True Then
' sheet exists
Else
' sheet does not exist
End If

========
and maybe set up an array of worksheet names and just cycle through them in nice
order:

Option Explicit
Sub testme()

Dim iRowCtr As Long
Dim destCell As Range

Dim mySheetNames(1 To 4) As String
mySheetNames(1) = "case"
mySheetNames(2) = "field"
mySheetNames(3) = "product"
mySheetNames(4) = "investment"

With Worksheets("masterfile")
For iRowCtr = 1 To 4
If WorksheetExists(mySheetNames(iRowCtr)) Then
'do nothing
Else
Worksheets.Add
ActiveSheet.Name = mySheetNames(iRowCtr)
End If
With Worksheets(mySheetNames(iRowCtr))
Set destCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(destCell) Then
'do nothing
Else
Set destCell = destCell.Offset(1, 0)
End If
End With
.Cells(iRowCtr, "A").Resize(1, 8).Copy _
Destination:=destCell
Next iRowCtr
End With
End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = Len(WB.Worksheets(SheetName).Name) 0
End Function


====

One thing you may want to think about is adding the worksheet name to a helper
column (G?).

Then you wouldn't have to worry about anyone rearranging the data and screwing
up your order.

PS. I changed the destination cell. I checked to see if it was empty. If it
was, I pasted right there. If not, I dropped down a row and then pasted. (You
always just dropped down a row--even on newly created worksheets.)


ricky wrote:

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

--

Dave Peterson

Ricky,

Here is another way. The macro reads col A in the master sheet, adds new sheets as needed and copies the data. As you will notice, the first row in newly added sheets will be empty, you will have to fix that if it bothers you.
The master sheet must be the active sheet when running the macro.

'-----
Option Explicit
' make sure that the master sheet is active when running this
Sub test597()
Dim srcRng As Range, cCell As Range
Dim shName As String

With Range("A1")
Set srcRng = Range(.Cells, .End(xlDown))
End With

For Each cCell In srcRng.Cells

' add sheets
On Error Resume Next
shName = cCell.Value
shName = Worksheets(shName).Name
If Err < 0 Then
On Error GoTo 0
With ActiveWorkbook.Sheets
.Add after:=.Item(Sheets.Count)
.Item(Sheets.Count).Name = shName
End With
End If

' enter data
With cCell
.CurrentRegion.Rows(.Row).Copy _
Destination:=Worksheets(shName). _
Cells(Rows.Count, "A").End(xlUp)(2)
End With
Next cCell

End Sub
'-----

Best regards
Anders Silven


"ricky" skrev i meddelandet 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

"Anders S" <anders wrote in message ...
Ricky,

Here is another way. The macro reads col A in the master sheet, adds new
sheets as needed and copies the data. As you will notice, the first row
in newly added sheets will be empty, you will have to fix that if it
bothers you.
The master sheet must be the active sheet when running the macro.

'-----
Option Explicit
' make sure that the master sheet is active when running this
Sub test597()
Dim srcRng As Range, cCell As Range
Dim shName As String

With Range("A1")
Set srcRng = Range(.Cells, .End(xlDown))
End With

For Each cCell In srcRng.Cells

' add sheets
On Error Resume Next
shName = cCell.Value
shName = Worksheets(shName).Name
If Err < 0 Then
On Error GoTo 0
With ActiveWorkbook.Sheets
.Add after:=.Item(Sheets.Count)
.Item(Sheets.Count).Name = shName
End With
End If

' enter data
With cCell
.CurrentRegion.Rows(.Row).Copy
Destination:=Worksheets(shName).
Cells(Rows.Count, "A").End(xlUp)(2)
End With
Next cCell

End Sub
'-----

Best regards
Anders Silven


"ricky" skrev i meddelandet
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


Thanks Anders Silven this works just I like wanted it to.
Thanks again.
ricky

Thanks Anders Silven this works just I like wanted it to.
Thanks again.
ricky

Thanks ricky, I'm glad it helped :)

After re-eading the code I made two small changes.
- by better qualifying srcRange the macro now runs from any sheet.
- added On Error GoTo 0 after the Ed If, otherwise On Error Resume Next would be still be active if Err = 0

'----
Option Explicit

Sub test597()
Dim srcRng As Range, cCell As Range
Dim shName As String

With Range("MasterFile!A1")
Set srcRng = Range(.Cells, .End(xlDown))
End With

For Each cCell In srcRng.Cells

' add sheets
On Error Resume Next
shName = cCell.Value
shName = Worksheets(shName).Name
If Err < 0 Then
On Error GoTo 0
With ActiveWorkbook.Sheets
.Add after:=.Item(Sheets.Count)
.Item(Sheets.Count).Name = shName
End With
End If
On Error GoTo 0

' enter data
With cCell
.CurrentRegion.Rows(.Row).Copy _
Destination:=Worksheets(shName). _
Cells(Rows.Count, "A").End(xlUp)(2)
End With
Next cCell

End Sub
'----

Best regards,
Anders Silven