Change destination of merged worksheet from current workbook to another

[lwhyatt]

In the formula below, I'd like to change the destination sheet from one called Merged in the current workbook to a new one in a workbook in a subfolder called 'Results'. I'd like to insert it at a defined cell reference.
Is this possible?



Option Explicit

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 "Merged" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Merged").Delete
On Error GoTo 0
Application.DisplayAlerts = True

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

'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

 

[sijpie]

Sure is possible.
Please when you post code, use the code tags. They are so beautiful in this new forum. In the icons just above where you type your post, there is an icon <vba/>. Click on that and then put your code inbetween the two tags that are inserted.

As for your question: do a search on opening another workbook . Then in your code you copy the sheet to the opened workbook, and rename it as required