Adding a new named worksheet causes my program to behave strangely....why?

[GooseLightning]

Hello Excel fans,

I am encountering a strange problem and I'm hoping someone may have a solution or explain what is happening.

I have written a program that takes a data file (rows and columns with headers) to extract specific columns and then sort the rows into named worksheets; nothing out of the ordinary. My program will:
1. create a number of named worksheets
2. parse specific (criteria-based; I want some not all) columns of data from original data sheet only to a TEMP_SHEET worksheet.
3. copy/paste all rows from the TEMP_SHEET data into corresponding named worksheets, based on cell criteria (e.g. if cell value in column D is 10 then that row will copy/paste to worksheet named "10").

When you open the workbook, there are 2 worksheets - "DATA" and "TEMP_SHEET". My program run excellently in this manner.

Effectively, this is a one-button VBA program to sort rows of data into named worksheets based on criteria.

THE PROBLEM:
I want to include creating the "TEMP_SHEET" as part of my subroutine that does the aforementioned work, such that when I open my workbook there is only the "DATA" worksheet, and the program will do the rest.

When I insert:

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets("DATA"))
ws.Name = "TEMP_SHEET"

at the top of my subroutine, or as its own subroutine, the rest of my program doesn't behave properly - it creates the extra named worksheets but the remaining tasks are not done properly. There is no error or compiling issues, the program just doesn't execute properly. How can adding these 3 lines of innocuous code drastically alter the execution of a solidly working program?

Does anyone have any suggestions as to why this is happening?

Thanks,

GooseLightning

 

[mumps]

Can you post the code you are currently using?

 

[wideboydixon]

Hi there,

Adding a new worksheet will most likely make it the active sheet which could well cause your existing VBA code to fail as it's assuming that the "DATA" sheet is the active sheet. How about:

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets("DATA"))
ws.Name = "TEMP_SHEET"
Sheets("DATA").Activate

WBD

 

[GooseLightning]

Thanks WBD,

You were very correct, in both application and in logic. I'm a little red-faced over the simplicity but I'm happy for the outcome! Thanks for your input!

Cheers,
Goose