Summary sheet

[Guest]

Hello:
I have some worksheets (questionnaire format) for each of the client. The
client name, phone number and other information are at the same locations in
each worksheet. My question is how I can create a summary sheet, so that each
row in the summary sheet contains one client information. I wrote the
following code, but I have no idea on how each of the client information can
be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4.
Sub Macro4()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])"
End Sub
Thanks in advance!

 

[Ron de Bruin]

Hi

Try this small example
Add a worksheet with the name "Master" to your testworkbook first and run this macro

If you only want to have the values see
http://www.rondebruin.nl/copy2.htm

Sub test()
Dim sh As Worksheet
Dim cell As Range
Dim a As Long
Dim rw As Long
rw = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
rw = rw + 1
a = 0
For Each cell In Range("A3,B3,C3")
a = a + 1
Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address
Next cell
End If
Next sh
End Sub

 

[Guest]

It works. Thanks a lot for your help!

Hi

Try this small example
Add a worksheet with the name "Master" to your testworkbook first and run this macro

If you only want to have the values see
http://www.rondebruin.nl/copy2.htm

Sub test()
Dim sh As Worksheet
Dim cell As Range
Dim a As Long
Dim rw As Long
rw = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
rw = rw + 1
a = 0
For Each cell In Range("A3,B3,C3")
a = a + 1
Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address
Next cell
End If
Next sh
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl

Hello:
I have some worksheets (questionnaire format) for each of the client. The
client name, phone number and other information are at the same locations in
each worksheet. My question is how I can create a summary sheet, so that each
row in the summary sheet contains one client information. I wrote the
following code, but I have no idea on how each of the client information can
be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4.
Sub Macro4()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])"
End Sub
Thanks in advance!

 

[Ron de Bruin]

You are welcome


--
Regards Ron de Bruin
http://www.rondebruin.nl

It works. Thanks a lot for your help!

Hi

Try this small example
Add a worksheet with the name "Master" to your testworkbook first and run this macro

If you only want to have the values see
http://www.rondebruin.nl/copy2.htm

Sub test()
Dim sh As Worksheet
Dim cell As Range
Dim a As Long
Dim rw As Long
rw = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
rw = rw + 1
a = 0
For Each cell In Range("A3,B3,C3")
a = a + 1
Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address
Next cell
End If
Next sh
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl

Hello:
I have some worksheets (questionnaire format) for each of the client. The
client name, phone number and other information are at the same locations in
each worksheet. My question is how I can create a summary sheet, so that each
row in the summary sheet contains one client information. I wrote the
following code, but I have no idea on how each of the client information can
be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4.
Sub Macro4()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])"
End Sub
Thanks in advance!

 

[Ron de Bruin]

I add a example to my website
http://www.rondebruin.nl/summary.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl

It works. Thanks a lot for your help!

Hi

Try this small example
Add a worksheet with the name "Master" to your testworkbook first and run this macro

If you only want to have the values see
http://www.rondebruin.nl/copy2.htm

Sub test()
Dim sh As Worksheet
Dim cell As Range
Dim a As Long
Dim rw As Long
rw = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
rw = rw + 1
a = 0
For Each cell In Range("A3,B3,C3")
a = a + 1
Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address
Next cell
End If
Next sh
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl

Hello:
I have some worksheets (questionnaire format) for each of the client. The
client name, phone number and other information are at the same locations in
each worksheet. My question is how I can create a summary sheet, so that each
row in the summary sheet contains one client information. I wrote the
following code, but I have no idea on how each of the client information can
be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4.
Sub Macro4()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])"
End Sub
Thanks in advance!

 

[Guest]

This post was brilliant and exactly what I needed for my situation...
However, I need the mergesheet to automatically format the columns so that
the data fits into the cells.. right now it's all scrunched up - so,
everytime I run the macro to update it - I would have to re-format/add column
headings to the mergesheet when I would rather it do it as part of the macro
run...

I also wonder if it's possible for it to automatically add
calculation/formula based on the data on the "new" mergesheet?

Again, this forum has been so immensely helpful and all of the people who
share their knowledge and time are to be highly commended!!

Thank you in advance

I add a example to my website
http://www.rondebruin.nl/summary.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl

It works. Thanks a lot for your help!

Hi

Try this small example
Add a worksheet with the name "Master" to your testworkbook first and run this macro

If you only want to have the values see
http://www.rondebruin.nl/copy2.htm

Sub test()
Dim sh As Worksheet
Dim cell As Range
Dim a As Long
Dim rw As Long
rw = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
rw = rw + 1
a = 0
For Each cell In Range("A3,B3,C3")
a = a + 1
Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address
Next cell
End If
Next sh
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



Hello:
I have some worksheets (questionnaire format) for each of the client. The
client name, phone number and other information are at the same locations in
each worksheet. My question is how I can create a summary sheet, so that each
row in the summary sheet contains one client information. I wrote the
following code, but I have no idea on how each of the client information can
be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4.
Sub Macro4()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])"
End Sub
Thanks in advance!

 

[Ron de Bruin]

Do you use this example now ?
http://www.rondebruin.nl/summary.htm

Add one line above the start row line

'Add headers
Newsh.Range("A11").Value = Array("header1", "header2", "header3", "header4")

'The links to the first sheet will start in row 2
RwNum = 1


It autofit the columns with this line
Newsh.UsedRange.Columns.AutoFit


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

This post was brilliant and exactly what I needed for my situation...
However, I need the mergesheet to automatically format the columns so that
the data fits into the cells.. right now it's all scrunched up - so,
everytime I run the macro to update it - I would have to re-format/add column
headings to the mergesheet when I would rather it do it as part of the macro
run...

I also wonder if it's possible for it to automatically add
calculation/formula based on the data on the "new" mergesheet?

Again, this forum has been so immensely helpful and all of the people who
share their knowledge and time are to be highly commended!!

Thank you in advance

I add a example to my website
http://www.rondebruin.nl/summary.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl

It works. Thanks a lot for your help!

:

Hi

Try this small example
Add a worksheet with the name "Master" to your testworkbook first and run this macro

If you only want to have the values see
http://www.rondebruin.nl/copy2.htm

Sub test()
Dim sh As Worksheet
Dim cell As Range
Dim a As Long
Dim rw As Long
rw = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
rw = rw + 1
a = 0
For Each cell In Range("A3,B3,C3")
a = a + 1
Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address
Next cell
End If
Next sh
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



Hello:
I have some worksheets (questionnaire format) for each of the client. The
client name, phone number and other information are at the same locations in
each worksheet. My question is how I can create a summary sheet, so that each
row in the summary sheet contains one client information. I wrote the
following code, but I have no idea on how each of the client information can
be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4.
Sub Macro4()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])"
End Sub
Thanks in advance!

 

[Guest]

Just in case I continue to have email problems!

I am using your example http://www.rondebruin.nl/copy2.htm (modified to copy
just values/formats)....

Will the below code still work in that example? If yes, could you let me
know where I should put it?

Do you use this example now ?
http://www.rondebruin.nl/summary.htm

Add one line above the start row line

'Add headers
Newsh.Range("A11").Value = Array("header1", "header2", "header3", "header4")

'The links to the first sheet will start in row 2
RwNum = 1


It autofit the columns with this line
Newsh.UsedRange.Columns.AutoFit


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

This post was brilliant and exactly what I needed for my situation...
However, I need the mergesheet to automatically format the columns so that
the data fits into the cells.. right now it's all scrunched up - so,
everytime I run the macro to update it - I would have to re-format/add column
headings to the mergesheet when I would rather it do it as part of the macro
run...

I also wonder if it's possible for it to automatically add
calculation/formula based on the data on the "new" mergesheet?

Again, this forum has been so immensely helpful and all of the people who
share their knowledge and time are to be highly commended!!

Thank you in advance

I add a example to my website
http://www.rondebruin.nl/summary.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



It works. Thanks a lot for your help!

:

Hi

Try this small example
Add a worksheet with the name "Master" to your testworkbook first and run this macro

If you only want to have the values see
http://www.rondebruin.nl/copy2.htm

Sub test()
Dim sh As Worksheet
Dim cell As Range
Dim a As Long
Dim rw As Long
rw = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
rw = rw + 1
a = 0
For Each cell In Range("A3,B3,C3")
a = a + 1
Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address
Next cell
End If
Next sh
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



Hello:
I have some worksheets (questionnaire format) for each of the client. The
client name, phone number and other information are at the same locations in
each worksheet. My question is how I can create a summary sheet, so that each
row in the summary sheet contains one client information. I wrote the
following code, but I have no idea on how each of the client information can
be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4.
Sub Macro4()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])"
End Sub
Thanks in advance!

 

[Ron de Bruin]

I reply to you private with a example

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Just in case I continue to have email problems!

I am using your example http://www.rondebruin.nl/copy2.htm (modified to copy
just values/formats)....

Will the below code still work in that example? If yes, could you let me
know where I should put it?

Do you use this example now ?
http://www.rondebruin.nl/summary.htm

Add one line above the start row line

'Add headers
Newsh.Range("A11").Value = Array("header1", "header2", "header3", "header4")

'The links to the first sheet will start in row 2
RwNum = 1


It autofit the columns with this line
Newsh.UsedRange.Columns.AutoFit


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

This post was brilliant and exactly what I needed for my situation...
However, I need the mergesheet to automatically format the columns so that
the data fits into the cells.. right now it's all scrunched up - so,
everytime I run the macro to update it - I would have to re-format/add column
headings to the mergesheet when I would rather it do it as part of the macro
run...

I also wonder if it's possible for it to automatically add
calculation/formula based on the data on the "new" mergesheet?

Again, this forum has been so immensely helpful and all of the people who
share their knowledge and time are to be highly commended!!

Thank you in advance

:

I add a example to my website
http://www.rondebruin.nl/summary.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



It works. Thanks a lot for your help!

:

Hi

Try this small example
Add a worksheet with the name "Master" to your testworkbook first and run this macro

If you only want to have the values see
http://www.rondebruin.nl/copy2.htm

Sub test()
Dim sh As Worksheet
Dim cell As Range
Dim a As Long
Dim rw As Long
rw = 0
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Master" Then
rw = rw + 1
a = 0
For Each cell In Range("A3,B3,C3")
a = a + 1
Sheets("Master").Cells(rw, a).Formula = "=" & sh.Name & "!" & cell.Address
Next cell
End If
Next sh
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



Hello:
I have some worksheets (questionnaire format) for each of the client. The
client name, phone number and other information are at the same locations in
each worksheet. My question is how I can create a summary sheet, so that each
row in the summary sheet contains one client information. I wrote the
following code, but I have no idea on how each of the client information can
be inserted into a new row, e.g. A3, B3, C3/A4, B4, C4.
Sub Macro4()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!RC[1])"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[1]C[1])"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(Sheet1!R[3]C[1])"
End Sub
Thanks in advance!