Collate results on some sheets into master with selective selection

[PhilThomas]

Hi,

I've been at this for 3 hours and its not going in. I've searched the board and google and have come up with various answers I've tried to adapt but I just can't get it to work.

Goal - To have a mastersheet built from a set of 12 named sheets (there are 20 named sheets in the workbook altogether). However only from a certain collection of rows and also ensuring that the header row is only copied over once.

So using

Sub copySheets()
Dim sh As Worksheet
Sheets("Master").Select
ActiveSheet.Cells.Clear

Dim i As Integer
i = 1
For Each sh In ThisWorkbook.Sheets
If (sh.Name = "A") Or (sh.Name = "V") Or (sh.Name = "NP") Or (sh.Name = "N") Then
sh.UsedRange.Copy


Range("A" & Rows.Count).End(xlUp).Offset(1 - i, 0).Select

ActiveSheet.Paste
i = 0
End If
Next sh
End Sub

Clears the Master sheet, checks only for the named sheets, but copies everything from the named sheets. Each sheet typically holds data from Col A to Col J and upto 30 rows.

I am only interested in the rows which have data in col F which can be anything.

I simply want to select and clear a sheet called Master. Take the headings from the first named sheet (as they are the same across all the sheets). Copy all of the entire rows which have an entry in Col F into the Master sheet. Move to the next named sheet. Ignore the header row (which always starts at a1) and copy all the information from the rows which have a value in col F and paste it into the Master sheet. Repeat for all named sheets.

I've tried messing around with a range select for the named sheet with a sh.range but keep getting error 1004 and its driving me mad.

any help much appreciated

Phil

 

[Trebor76]

Hi Phil,

Try this:

Option Explicit
Sub CopySheets()

    'http://www.mrexcel.com/forum/showthread.php?t=626943

    Dim varMySheet As Variant
    Dim wstConsSheet As Worksheet, _
        wstMySheet As Worksheet
    Dim strDataCol As String, _
        strConsCol As String
    Dim intLoopCounter As Integer
    
    Set wstConsSheet = Sheets("Master") 'Sheet name for the consolidation. Change to suit.
    strDataCol = "F" 'Column containing data to be copied into the 'wstConsSheet' sheet. Change to suit.
    strConsCol = "A" 'Column for the 'strDataCol' to be consolidated into. Change to suit.
    
    Application.ScreenUpdating = False
    
    For Each varMySheet In Array("A", "V", "NP", "N") 'Sheet name(s) containing data. Change to suit.
        'If we are at the first copy, then...
        If intLoopCounter = 0 Then
            '...clear any existing data in the 'wstConsSheet' tab and copy in the data from Row down to the last row found _
            in the 'strDataCol' column into the 'wstConsSheet' sheet.
            wstConsSheet.Cells.Clear
            Sheets(varMySheet).Range(strDataCol & "1:" & strDataCol & Sheets(varMySheet).Range(strDataCol & Rows.Count).End(xlUp).Row).Copy _
                    Destination:=wstConsSheet.Cells(1, strConsCol)
        'Else...
        Else
            '...copy in the data from Row 2 down to the last row found in the 'strDataCol' column into the 'wstConsSheet' sheet.
            Sheets(varMySheet).Range(strDataCol & "2:" & strDataCol & Sheets(varMySheet).Range(strDataCol & Rows.Count).End(xlUp).Row).Copy _
                    Destination:=wstConsSheet.Cells(Rows.Count, strConsCol).End(xlUp)(2)
        End If
        intLoopCounter = intLoopCounter + 1 'Increment counter
    Next varMySheet
    
    Application.ScreenUpdating = True
    
    Set wstConsSheet = Nothing
        
End Sub

Regards,

Robert

 

[PhilThomas]

Thanks Robert,

Commented as well - wonderful

I copied and run the code, but it only copies over Col F into Col A. I want to copy over all of the row but only if that row has a value in F.

I shall have a look at changing it, unless you can suggest a quick tweak?

Phil

 

[Trebor76]

Hey Phil,

See how this goes:

Option Explicit
Sub CopyEntireRows()

    'http://www.mrexcel.com/forum/showthread.php?t=626943

    Dim varMySheet As Variant
    Dim wstConsSheet As Worksheet
    Dim strDataCol As String, _
        strConsCol As String
    Dim rngCell As Range
    Dim intLoopCounter As Integer
    
    Set wstConsSheet = Sheets("Master") 'Sheet name for the consolidation. Change to suit.
    strDataCol = "F" 'Column containing data to be copied into the 'wstConsSheet' sheet. Change to suit.
    strConsCol = "A" 'Column for the 'strDataCol' to be consolidated into. Change to suit.
    
    Application.ScreenUpdating = False
    
    For Each varMySheet In Array("A", "V", "NP", "N") 'Sheet name(s) containing data. Change to suit.
        'If we are at the first copy, then...
        If intLoopCounter = 0 Then
            '...clear any existing data in the 'wstConsSheet' tab and copy in the data from Row 1 (as these are the headings) in the 'strConsCol' column.
            With wstConsSheet
                .Cells.Clear
                .Cells(1, strConsCol).Value = Sheets(varMySheet).Range(strDataCol & "1").Value
            End With
        End If
        'Copy the entire row if the cell in the 'strDataCol' column has a value in it.
        For Each rngCell In Sheets(varMySheet).Range(strDataCol & "2:" & strDataCol & Sheets(varMySheet).Range(strDataCol & Rows.Count).End(xlUp).Row)
            If Len(rngCell) > 0 Then
                Rows(rngCell.Row).EntireRow.Copy _
                    Destination:=wstConsSheet.Cells(Rows.Count, strConsCol).End(xlUp)(2)
            End If
        Next rngCell
        intLoopCounter = intLoopCounter + 1 'Increment counter
    Next varMySheet
    
    Application.ScreenUpdating = True
    
    Set wstConsSheet = Nothing
        
End Sub

Regards,

Robert

 

[PhilThomas]

Great, thanks Robert

 

[Trebor76]

You're welcome