Excel: Paste data from several worksheets into one

[timleonard]

I am trying to copy the contents of 6 to 10 worksheets and paste them into one called "coverpage" I have been working with the following code, I've managed to get it to paste data to the coverpage with formatting, but it will only put it in cell A1 and overwrite what’s there. I can't seem to get it to paste after the last row.

Can someone help me out or give some suggestions as to how this can be done

Sub CoverPage()

    Dim wsPaste As Worksheet
    Dim ws      As Worksheet
    
    Set wsPaste = Worksheets("CoverPage")
    
    '***Delete All Rows on CoverPage***
    Worksheets("CoverPage").Rows.Delete     'ClearContents

    '***Copy visible cells from all worksheets***
    For Each ws In Worksheets
        
       'checks if looping sheets are not called CoverPage and thus ignore it
       If ws.Name <> wsPaste.Name Then
            
            LastRow = Range("B1").End(xlDown).Row
            ws.Range("B1:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy
                
            'move to the last cell with data to paste
            LastCell = Range("A:A").End(xlUp).Row
            wsPaste.Range("A" & LastCell).Select
           
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
                 xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
                 xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False

       End If

    Next ws

End Sub

I truly would appreciate any help that could be given

[Guido Geurs]

this will do the work:

       If ws.Name <> wsPaste.Name Then
  
            LastRow = Range("B1").End(xlDown).Row
            ws.Range("B1:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy
  
            'move to the last cell with data to paste

            Range("A1").Select
            If Range("A1").Value <> "" Then _
                 Range("A" & (Range("A1").End(xlDown).Row + 1)).Select

 
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
                 xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
                 xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
  
       End If

[timleonard]

this will do the work:

       If ws.Name <> wsPaste.Name Then
  
            LastRow = Range("B1").End(xlDown).Row
            ws.Range("B1:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy
  
            'move to the last cell with data to paste

            Range("A1").Select
            If Range("A1").Value <> "" Then _
                 Range("A" & (Range("A1").End(xlDown).Row + 1)).Select

 
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
                 xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
                 xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
  
       End If

Works Great...Thanks so much