PasteSpecial (Column Widths & Values) from multiple sheets into one master sheet VBA

[mhockey88]

Hi there - I am trying to use VBA to take each of 15 tabs in my workbook, paste the data into a master sheet (as values and maintaining column widths) and then delete the original tabs after that

I'm having trouble as I can't figure out how to use pastespecial in combination with End(xlUp), as I would like the data from each successive tab to paste in the next empty row

Thanks in advance for all the help!

 

[MARK858]

You do realize that the columns will resize to whatever size the last sheets copied columns are?

Uncomment

ws.Delete

once you know the rest of the code is working.

Change Sheets("Master") to your mastersheets name.

Sub yyy()
    Dim ws As Worksheet, lc As Long, lr As Long
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            lc = ws.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
            lr = ws.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row
            ws.Range(ws.Cells(1, 1), ws.Cells(lr, lc)).Copy
            
            With Sheets("Master").Range("A1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                .PasteSpecial xlPasteColumnWidths
                .PasteSpecial xlPasteValues, , False, False
            End With
            
            Application.CutCopyMode = False

            Application.DisplayAlerts = False
            'ws.Delete
            Application.DisplayAlerts = True

        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[mhockey88]

Thanks Mark. As far as the column resizing I would've thought so, but I think something odd must have been going on a few of the files which for some reason was fixed by pasting as values and column widths. Regardless, I've used your code and it's working perfectly, thanks for the help!