Hello,
I am very new VBA coding, and I am having a little bit of trouble perfecting this code.
I know the code works as I've run it, and I know where the error is, I'm just not sure how to fix it.
My excel sheet already has a summary tab, which needs to stay.
I have imported eight sheets of new data from external workbooks (successfully).
Now, I want a macro that adds a new worksheet, and imports the data from these eight worksheets into this new sheet and deletes the originating worksheets.
I can see the new worksheet creating, and that the data is importing and deleting the old sheets.
However, in my code (below) it either copies in the data from my original summary sheet incorrectly or deletes my new worksheet, depending on what sheet I name in this line of code (combine or summary)
If wsSrc.Name <> "Combine" Then
This is the line of code the needs work, and is highlighted below in the full code example
I am very new VBA coding, and I am having a little bit of trouble perfecting this code.
I know the code works as I've run it, and I know where the error is, I'm just not sure how to fix it.
My excel sheet already has a summary tab, which needs to stay.
I have imported eight sheets of new data from external workbooks (successfully).
Now, I want a macro that adds a new worksheet, and imports the data from these eight worksheets into this new sheet and deletes the originating worksheets.
I can see the new worksheet creating, and that the data is importing and deleting the old sheets.
However, in my code (below) it either copies in the data from my original summary sheet incorrectly or deletes my new worksheet, depending on what sheet I name in this line of code (combine or summary)
If wsSrc.Name <> "Combine" Then
This is the line of code the needs work, and is highlighted below in the full code example
Code:
Sub CombineWorksheets()
'Macro to combine imported worksheets into single new worksheet
'Written by Shara Hazel 11/7/17
'Create a new worksheet
Dim ws As Worksheet
Dim newSheetName As String
newSheetName = "Combined"
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = newSheetName
End With
'Set up for creation of loop
Dim wsSrc As Worksheet
Dim wsDest As Worksheet
Dim rngDest As Range
Dim lastRow As Long
Dim destRow As Long
Set wsDest = Worksheets("Combined")
Set rngDest = wsDest.Range("A2")
Application.DisplayAlerts = False 'supress promp for worksheet deletes
'loop through all source sheets in this workbook
For Each wsSrc In ThisWorkbook.Sheets
[COLOR=#ff0000] If wsSrc.Name <> "Combine" Then[/COLOR]
lastRow = wsSrc.Cells.SpecialCells(xlCellTypeLastCell).Row
wsSrc.Range("A1", wsSrc.Range("O" & lastRow)).Copy Destination:=rngDest
Set rngDest = rngDest.Offset(lastRow - 1)
wsSrc.Delete 'delete the source worksheet without a prompt
End If
Next
Application.DisplayAlerts = True
End Sub
[\code]
Thanks in advance for any help you are able to provide