Help required with 'combing worksheet dat'a code

sharajay

New Member
Joined
Jul 12, 2017
Messages
2
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

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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi. Your problem seems to be that you create your sheet calling it 'Combined' then look to delete a sheet not called 'Combine'.
 
Upvote 0
Hi,
Yes, I want to create a sheet called 'combined'. I then want to import data from a number of other sheets, and delete the sheets I am importing data from. This is working correctly.

The problem is, I already ave a summary sheet set up (where I am going to enter totals etc) I don't want this sheets data to import into combined, not do I want it to be deleted. I can't figure out how to amend the code to ignore this 'summary' sheet
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top