How do you copy items from many worksheets to one?

[White49]

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?

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
>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]

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

[White49]

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]

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