I own a small company where my we get inquiries on a daily business
and we open the .csv files with rows like the following. An employee
places each in individual worksheets in one workbook (normally there
are approx. 12 rows of information):

A B
1 Information Input Column
2 Inquirer John Doe
3 Phone Number 123-123-1234
4 Email

We have 80-100 worksheets in one document at the end of the month.

My database is the first worksheet in the workbook, called
DBworksheet, followed by the 80-100 individual worksheets of
inquiries.

The header of the DBworksheet is:

INQUIRER STNUMBER STNAME CITY
STATE ZIP

The first inquiry worksheet, which is the second worksheet has a tab
titled Doe_John

To populate the first row under the header with the information of the
Doe_John worksheet, I have the formulas:

=Doe_John!$B$2 =Doe_John!$B$3 =Doe_John!$B$4

As of now, I am scrolling down and filling the number of rows with the
number of worksheets I have and manually doing a repair/replace in the
function.

For instance, the 2nd inquiry worksheet is Smith_Joyce. After using
fill I highlight the copied Doe_John row and find/replace by:

Find: Doe_John
Replace: Smith_Joyce

Each function with Doe_John is replaced with Smith_Joyce:

=Smith_Joyce!$B$2 =Smith_Joyce!$B$3 =Smith_Joyce!$B$4

My row after John Doe is populated now with Joyce Smith.

Is there a way I can create a macro that counts how many inqury
worksheets I have, fills the rows and then does a find/replace based
on each tab name so I don't have to do it manually?

Thanks,

Dan

Can you explain why exactly you are creating 60+ worksheets with 1 worksheet
per person?

"Dan" wrote:


I own a small company where my we get inquiries on a daily business
and we open the .csv files with rows like the following. An employee
places each in individual worksheets in one workbook (normally there
are approx. 12 rows of information):

A B
1 Information Input Column
2 Inquirer John Doe
3 Phone Number 123-123-1234
4 Email

We have 80-100 worksheets in one document at the end of the month.

My database is the first worksheet in the workbook, called
DBworksheet, followed by the 80-100 individual worksheets of
inquiries.

The header of the DBworksheet is:

INQUIRER STNUMBER STNAME CITY
STATE ZIP

The first inquiry worksheet, which is the second worksheet has a tab
titled Doe_John

To populate the first row under the header with the information of the
Doe_John worksheet, I have the formulas:

=Doe_John!$B$2 =Doe_John!$B$3 =Doe_John!$B$4

As of now, I am scrolling down and filling the number of rows with the
number of worksheets I have and manually doing a repair/replace in the
function.

For instance, the 2nd inquiry worksheet is Smith_Joyce. After using
fill I highlight the copied Doe_John row and find/replace by:

Find: Doe_John
Replace: Smith_Joyce

Each function with Doe_John is replaced with Smith_Joyce:

=Smith_Joyce!$B$2 =Smith_Joyce!$B$3 =Smith_Joyce!$B$4

My row after John Doe is populated now with Joyce Smith.

Is there a way I can create a macro that counts how many inqury
worksheets I have, fills the rows and then does a find/replace based
on each tab name so I don't have to do it manually?

Thanks,

Dan

I created formulas like you did in the example. You can also hard copy the
value into the DBworksheet. I did only three rows of data, you can add up to
the 10 rows as needed.


Sub create_db()

RowCount = 1
For Each sht In ThisWorkbook.Sheets
If sht.Name < "DBworksheet" Then
With Sheets("DBworksheet")
.Range("A" & RowCount).Formula = _
"=" & sht.Name & "!$B$2"
.Range("B" & RowCount).Formula = _
"=" & sht.Name & "!$B$3"
.Range("C" & RowCount).Formula = _
"=" & sht.Name & "!$B$4"
End With
RowCount = RowCount + 1
End If
next sht
End Sub


"Dan" wrote:


I own a small company where my we get inquiries on a daily business
and we open the .csv files with rows like the following. An employee
places each in individual worksheets in one workbook (normally there
are approx. 12 rows of information):

A B
1 Information Input Column
2 Inquirer John Doe
3 Phone Number 123-123-1234
4 Email

We have 80-100 worksheets in one document at the end of the month.

My database is the first worksheet in the workbook, called
DBworksheet, followed by the 80-100 individual worksheets of
inquiries.

The header of the DBworksheet is:

INQUIRER STNUMBER STNAME CITY
STATE ZIP

The first inquiry worksheet, which is the second worksheet has a tab
titled Doe_John

To populate the first row under the header with the information of the
Doe_John worksheet, I have the formulas:

=Doe_John!$B$2 =Doe_John!$B$3 =Doe_John!$B$4

As of now, I am scrolling down and filling the number of rows with the
number of worksheets I have and manually doing a repair/replace in the
function.

For instance, the 2nd inquiry worksheet is Smith_Joyce. After using
fill I highlight the copied Doe_John row and find/replace by:

Find: Doe_John
Replace: Smith_Joyce

Each function with Doe_John is replaced with Smith_Joyce:

=Smith_Joyce!$B$2 =Smith_Joyce!$B$3 =Smith_Joyce!$B$4

My row after John Doe is populated now with Joyce Smith.

Is there a way I can create a macro that counts how many inqury
worksheets I have, fills the rows and then does a find/replace based
on each tab name so I don't have to do it manually?

Thanks,

Dan

Right or wrong, that's how the web-form returns and the process
evolved.

Further, that is how we start our account paperwork, printing each one
is our cover.


On Tue, 1 Apr 2008 22:56:02 -0700, akphidelt
wrote:

Can you explain why exactly you are creating 60+ worksheets with 1 worksheet
per person?

"Dan" wrote:


I own a small company where my we get inquiries on a daily business
and we open the .csv files with rows like the following. An employee
places each in individual worksheets in one workbook (normally there
are approx. 12 rows of information):

A B
1 Information Input Column
2 Inquirer John Doe
3 Phone Number 123-123-1234
4 Email

We have 80-100 worksheets in one document at the end of the month.

My database is the first worksheet in the workbook, called
DBworksheet, followed by the 80-100 individual worksheets of
inquiries.

The header of the DBworksheet is:

INQUIRER STNUMBER STNAME CITY
STATE ZIP

The first inquiry worksheet, which is the second worksheet has a tab
titled Doe_John

To populate the first row under the header with the information of the
Doe_John worksheet, I have the formulas:

=Doe_John!$B$2 =Doe_John!$B$3 =Doe_John!$B$4

As of now, I am scrolling down and filling the number of rows with the
number of worksheets I have and manually doing a repair/replace in the
function.

For instance, the 2nd inquiry worksheet is Smith_Joyce. After using
fill I highlight the copied Doe_John row and find/replace by:

Find: Doe_John
Replace: Smith_Joyce

Each function with Doe_John is replaced with Smith_Joyce:

=Smith_Joyce!$B$2 =Smith_Joyce!$B$3 =Smith_Joyce!$B$4

My row after John Doe is populated now with Joyce Smith.

Is there a way I can create a macro that counts how many inqury
worksheets I have, fills the rows and then does a find/replace based
on each tab name so I don't have to do it manually?

Thanks,

Dan

Thanks, I'll give it a try!

On Wed, 2 Apr 2008 04:25:01 -0700, Joel
wrote:

I created formulas like you did in the example. You can also hard copy the
value into the DBworksheet. I did only three rows of data, you can add up to
the 10 rows as needed.


Sub create_db()

RowCount = 1
For Each sht In ThisWorkbook.Sheets
If sht.Name < "DBworksheet" Then
With Sheets("DBworksheet")
.Range("A" & RowCount).Formula = _
"=" & sht.Name & "!$B$2"
.Range("B" & RowCount).Formula = _
"=" & sht.Name & "!$B$3"
.Range("C" & RowCount).Formula = _
"=" & sht.Name & "!$B$4"
End With
RowCount = RowCount + 1
End If
next sht
End Sub


"Dan" wrote:


I own a small company where my we get inquiries on a daily business
and we open the .csv files with rows like the following. An employee
places each in individual worksheets in one workbook (normally there
are approx. 12 rows of information):

A B
1 Information Input Column
2 Inquirer John Doe
3 Phone Number 123-123-1234
4 Email

We have 80-100 worksheets in one document at the end of the month.

My database is the first worksheet in the workbook, called
DBworksheet, followed by the 80-100 individual worksheets of
inquiries.

The header of the DBworksheet is:

INQUIRER STNUMBER STNAME CITY
STATE ZIP

The first inquiry worksheet, which is the second worksheet has a tab
titled Doe_John

To populate the first row under the header with the information of the
Doe_John worksheet, I have the formulas:

=Doe_John!$B$2 =Doe_John!$B$3 =Doe_John!$B$4

As of now, I am scrolling down and filling the number of rows with the
number of worksheets I have and manually doing a repair/replace in the
function.

For instance, the 2nd inquiry worksheet is Smith_Joyce. After using
fill I highlight the copied Doe_John row and find/replace by:

Find: Doe_John
Replace: Smith_Joyce

Each function with Doe_John is replaced with Smith_Joyce:

=Smith_Joyce!$B$2 =Smith_Joyce!$B$3 =Smith_Joyce!$B$4

My row after John Doe is populated now with Joyce Smith.

Is there a way I can create a macro that counts how many inqury
worksheets I have, fills the rows and then does a find/replace based
on each tab name so I don't have to do it manually?

Thanks,

Dan