I'm having trouble using the help you've provided. I'm very thankful, but I
also rather ignorant of programming. What exactly do I do with the code
you've provided?
--
Thanks
"Bill Pfister" wrote:
>
> Public Sub CombineSheets()
> Dim wkb As Workbook
> Dim rngA As Range
> Dim rngB As Range
> Dim rngC As Range
> Dim colItems As New Collection
> Dim i As Long
>
> Set wkb = ThisWorkbook
>
> Set rngA = wkb.Worksheets("shtA").Range("A1")
> Set rngB = wkb.Worksheets("shtB").Range("A1")
> Set rngC = wkb.Worksheets("shtC").Range("A1")
>
> Call CombineSheetsIndividual(colItems, rngA, "ValueA")
> Call CombineSheetsIndividual(colItems, rngB, "ValueB")
>
> rngC.Parent.Range(rngC.Row & ":" & _
> rngC.SpecialCells(xlCellTypeLastCell).Row).Delete
> Set rngC = wkb.Worksheets("shtC").Range("A1")
>
> For i = 1 To colItems.Count
> rngC.Offset(i - 1, 0).Value = colItems.Item(i).Item("Name")
> rngC.Offset(i - 1, 1).Value = colItems.Item(i).Item("ValueA")
> rngC.Offset(i - 1, 2).Value = colItems.Item(i).Item("ValueB")
> Next i
> End Sub
>
>
>
> Public Sub CombineSheetsIndividual( _
> colItems As Collection, rng As Range, strValueKey As String)
> Dim strKey As String
> Dim dblValue As Double
> Dim lngCount As Long
> Dim i As Long
>
> lngCount = rng.SpecialCells(xlCellTypeLastCell).Row
>
> For i = 0 To lngCount - 1
> strKey = rng.Offset(i, 0).Value
>
> If (Len(strKey) > 0) Then
> If (Not (CollectionKeyExists(colItems, strKey))) Then
> Call colItems.Add(New Collection, strKey)
> Call colItems.Item(strKey).Add(strKey, "Name")
> Call colItems.Item(strKey).Add("", "ValueA")
> Call colItems.Item(strKey).Add("", "ValueB")
> End If
>
> If (Len(rng.Offset(i, 1).Value) > 0) Then
> dblValue = rng.Offset(i, 1).Value
> Call colItems.Item(strKey).Remove(strValueKey)
> Call colItems.Item(strKey).Add(dblValue, strValueKey)
> End If
> End If
> Next i
> End Sub
>
>
>
>
> Public Function CollectionKeyExists( _
> col As Collection, strKey As String) As Boolean
> Dim varCheck As Variant
>
> On Error GoTo ErrHandler
>
> Set varCheck = col.Item(strKey)
>
> CollectionKeyExists = True
>
> Exit Function
> ErrHandler:
> CollectionKeyExists = False
> End Function
>
>
>
>
>
> "bearcomp" wrote:
>
> > After re-reading my quesiton, I realized that I wasn't clear. What I want
> > to do is create a third worksheet that contains cells from sheetA and sheetB.
> > SheetC would be a single sheet that would consolidate both sheetA and sheetB
> > so that all of the data would be in a single sheet.
> >
> > For example:
> >
> > Sheet A says Sheet B says
> > Unique list: Data 1 Unique list Data 2
> > A 1 A
> > 2
> > B B
> > 3
> > C 4 C
> >
> > _______________________________________________
> > I want Sheet C to say
> > Unique list: Data 1 Data 2
> > A 1 2
> > B 3
> > C 4
> >
> > My sheets contain many columns that I want to move simultaneously. It's a
> > complicated copy and paste function that I'm looking for.
> >
> > Thanks
> >
> >
> > "Bill Pfister" wrote:
> >
> > >
> > > There are several methods to do this formulaically without VBA. Here is
> > > one: First thing is to get a unique list of the names from the two
> > > worksheets. Then sum SumIf functions, one for each worksheet you are
> > > referencing, with the unique list as the criteria.
> > >
> > > For example, SheetA contains one list and SheetB contains another. SheetSum
> > > is your consolidation sheet. Assume all sheets have similar layout (names in
> > > column A, values in columns B.
> > >
> > > SheetSum!B1 formula: “=sumif( SheetA!$A$1:$A$100, A1, SheetA!B$1:B$100 ) +
> > > sumif( SheetB!$A$1:$A$100, A1, SheetB!B$1:B$100 )”
> > >
> > >
> > > "bearcomp" wrote:
> > >
> > > > I have two worksheets with data related to specific people. The people
> > > > contained in the sheets are similar, but there are some differences. I want
> > > > to create a single worksheet that adds the data from the two sheets together
> > > > so that data for each person is combined into a single row.
> > > >
> > > > How?
> > > > --
> > > > Thanks
|
Similar Threads
