How do you copy items from many worksheets to one?
 

I have over 100 worksheets in an excel file I am working with. It is a
database of people profiles for alumni of my fraternity. Each worksheet has
the exact same formatting with the same type of information in corresponding
cells (i.e. B2 contains the person's name in each sheet). I'm trying to set
up a master sheet I can use as a database for mail merges in word. Is there
anyway I can copy the information to one sheet without going into each sheet
individually and copying and pasting?

How do you copy items from many worksheets to one?
 

Hi White49

You can create links or Copy
I have a example for both on my site

http://www.rondebruin.nl/copy2.htm
And with formulas
http://www.rondebruin.nl/summary.htm

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



"White49" wrote in message ...
I have over 100 worksheets in an excel file I am working with. It is a
database of people profiles for alumni of my fraternity. Each worksheet has
the exact same formatting with the same type of information in corresponding
cells (i.e. B2 contains the person's name in each sheet). I'm trying to set
up a master sheet I can use as a database for mail merges in word. Is there
anyway I can copy the information to one sheet without going into each sheet
individually and copying and pasting?

How do you copy items from many worksheets to one?
 

I think I'd use a macro to build formulas to extract the values.

Like this formula:
=if(sheet2!B2="","",sheet2!b2)

If that seems like something you want to try:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim NewWks As Worksheet
Dim myAddresses As Variant
Dim iCtr As Long
Dim DestCell As Range
Dim HowManyCells As Long
Dim iCol As Long

'which cells?
myAddresses = Array("B2", "c9", "d12", "B3")
HowManyCells = UBound(myAddresses) - LBound(myAddresses) + 1

Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Value = "Worksheet Name"
.Range("B1").Resize(1, HowManyCells).Value = myAddresses
Set DestCell = .Range("a2")
End With

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = NewWks.Name Then
'do nothing
Else
With DestCell
.Value = "'" & wks.Name
iCol = 0
For iCtr = LBound(myAddresses) To UBound(myAddresses)
iCol = iCol + 1
.Offset(0, iCol).Formula = "=if('" & wks.Name & "'!" _
& myAddresses(iCtr) & "="""",""""," _
& "'" & wks.Name & "'!" & myAddresses(iCtr) & ")"
Next iCtr
End With
Set DestCell = DestCell.Offset(1, 0)
End If
Next wks

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


White49 wrote:

I have over 100 worksheets in an excel file I am working with. It is a
database of people profiles for alumni of my fraternity. Each worksheet has
the exact same formatting with the same type of information in corresponding
cells (i.e. B2 contains the person's name in each sheet). I'm trying to set
up a master sheet I can use as a database for mail merges in word. Is there
anyway I can copy the information to one sheet without going into each sheet
individually and copying and pasting?

--

Dave Peterson

How do you copy items from many worksheets to one?
 

I'd be all for trying a macro. Will this work if I named my worksheets
according to the person's name?

"Dave Peterson" wrote:

I think I'd use a macro to build formulas to extract the values.

Like this formula:
=if(sheet2!B2="","",sheet2!b2)

If that seems like something you want to try:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim NewWks As Worksheet
Dim myAddresses As Variant
Dim iCtr As Long
Dim DestCell As Range
Dim HowManyCells As Long
Dim iCol As Long

'which cells?
myAddresses = Array("B2", "c9", "d12", "B3")
HowManyCells = UBound(myAddresses) - LBound(myAddresses) + 1

Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Value = "Worksheet Name"
.Range("B1").Resize(1, HowManyCells).Value = myAddresses
Set DestCell = .Range("a2")
End With

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = NewWks.Name Then
'do nothing
Else
With DestCell
.Value = "'" & wks.Name
iCol = 0
For iCtr = LBound(myAddresses) To UBound(myAddresses)
iCol = iCol + 1
.Offset(0, iCol).Formula = "=if('" & wks.Name & "'!" _
& myAddresses(iCtr) & "="""",""""," _
& "'" & wks.Name & "'!" & myAddresses(iCtr) & ")"
Next iCtr
End With
Set DestCell = DestCell.Offset(1, 0)
End If
Next wks

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


White49 wrote:

I have over 100 worksheets in an excel file I am working with. It is a
database of people profiles for alumni of my fraternity. Each worksheet has
the exact same formatting with the same type of information in corresponding
cells (i.e. B2 contains the person's name in each sheet). I'm trying to set
up a master sheet I can use as a database for mail merges in word. Is there
anyway I can copy the information to one sheet without going into each sheet
individually and copying and pasting?

--

Dave Peterson

How do you copy items from many worksheets to one?
 

You're soon gonna find out, huh?

White49 wrote:

I'd be all for trying a macro. Will this work if I named my worksheets
according to the person's name?

"Dave Peterson" wrote:

I think I'd use a macro to build formulas to extract the values.

Like this formula:
=if(sheet2!B2="","",sheet2!b2)

If that seems like something you want to try:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim NewWks As Worksheet
Dim myAddresses As Variant
Dim iCtr As Long
Dim DestCell As Range
Dim HowManyCells As Long
Dim iCol As Long

'which cells?
myAddresses = Array("B2", "c9", "d12", "B3")
HowManyCells = UBound(myAddresses) - LBound(myAddresses) + 1

Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Value = "Worksheet Name"
.Range("B1").Resize(1, HowManyCells).Value = myAddresses
Set DestCell = .Range("a2")
End With

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = NewWks.Name Then
'do nothing
Else
With DestCell
.Value = "'" & wks.Name
iCol = 0
For iCtr = LBound(myAddresses) To UBound(myAddresses)
iCol = iCol + 1
.Offset(0, iCol).Formula = "=if('" & wks.Name & "'!" _
& myAddresses(iCtr) & "="""",""""," _
& "'" & wks.Name & "'!" & myAddresses(iCtr) & ")"
Next iCtr
End With
Set DestCell = DestCell.Offset(1, 0)
End If
Next wks

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


White49 wrote:

I have over 100 worksheets in an excel file I am working with. It is a
database of people profiles for alumni of my fraternity. Each worksheet has
the exact same formatting with the same type of information in corresponding
cells (i.e. B2 contains the person's name in each sheet). I'm trying to set
up a master sheet I can use as a database for mail merges in word. Is there
anyway I can copy the information to one sheet without going into each sheet
individually and copying and pasting?

--

Dave Peterson


--

Dave Peterson