Results 1 to 9 of 9

Combining some sheets into another sheet

Threaded View

  1. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Combining some sheets into another sheet

    Untested (ie coded on the fly) but perhaps:

    Sub Collate()
    Dim ws As Worksheet, wsFinal As Worksheet, rngCopy As Range, xlCalc As xlCalculation
    On Error GoTo ExitPoint
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set wsFinal = Sheets("FCombined")
    With wsFinal
        .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A")).Resize(,13).Clear
    End With
    For Each ws In ThisWorkbook.Worksheets
        Select Case UCase(ws.Name)
            Case "F1", "F2", "F3", "F4"
                With ws
                    Set rngCopy = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 13)
                End With
                With wsFinal
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count) = rngCopy.Value
                End With
        End Select
    Next ws
    ExitPoint:
    Set wsFinal = Nothing
    With Application
        .Calculation = xlCalc
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    End Sub

    the above assumes

    a) final sheet has header row

    b) final sheet should be purged with each collation

    c) values only copy


    EDIT: 12:14 - missing comma in the .Clear line at beginning of routine - did say it was untested
    Last edited by DonkeyOte; 03-11-2010 at 08:14 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1