VBA to combine worksheets into one new worksheet in a workbook

[lwhyatt]

Hi All,

Can anybody help in amending the VBA below to paste the new worksheet created into a specified workbook at a given cell reference? I'd also like to omit pasting the contents of Column A.
Many thanks ...

Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

'Fill in the start row
StartRow = 2

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Sheets(Array("XS BUT USED", "ZERO USAGE", "HOSIERY", "DRESSINGS", "APPLIANCES"))


'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row >= StartRow copy the CopyRng
If shLast > 0 And shLast >= StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If


'This example copies values/formats, if you only want to copy the
'values or want to copy everything look below example 1 on this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If


Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

 

[vcoolio]

Hello IWhyatt,

You have alot of code there just to copy/paste data from multiple worksheets to one master worksheet. Rather than continuosly deleting the master sheet, may I suggest that you retain the master worksheet (RDBMergeSheet) with its headings and simply clear it whenever you need to paste new data into it. Something as follows may save you unnecessary use of resource:-

Sub Test()
        
        Dim ws As Worksheet
        Dim wsRDB As Worksheet: Set wsRDB = Sheets("RDBMergeSheet")

Application.ScreenUpdating = False
        
        wsRDB.UsedRange.Offset(1).Clear
        
        For Each ws In Worksheets
                If ws.Name <> "RDBMergeSheet" Then
                ws.UsedRange.Offset(1, 1).Copy
                wsRDB.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                End If
        Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Please test it in a copy of your actual workbook first.

The code does not copy/paste Column A values from any sheet.

I've attached a mock-up workbook to show you how it works. Click on the "Test Run" button to see it work. Alter the amount of data in the source sheets if you wish then click again on the button to see the updated data transfer.

I hope that this helps.

Cheerio,
vcoolio.

Here's the link to the mock-up:-

Ge.tt | Gett sharing

Ge.tt lets you share images, documents, videos and files with one click. Your files are available instantly with our unique real-time technology. The smallest step in file sharing.

ge.tt

 

[lwhyatt]

Thankyou!

 

[vcoolio]

You're welcome IWhyatt.
I hope that it all worked out for you.

Cheerio,
vcoolio.