2 Small VBA Questions; Re: Text To Columns and Naming First Sheet

[Guest]

Good morning,

First VBA Scenario,
I have a worsksheet where I use a macro to do a "Text To Columns" spread of
data by delimeted width into the next column.

When I run the macro, a dialogue box pops up to prompt if I want to
overwrite the cells in the next column. I need VBA code that will force this
overwrite so the macro will not pause and prompt me to overwrite. Any ideas?

Next VBA Scenario,
I get a weekly spreadsheet from our planning department that has several
tabs. The first tab is usually named "Data - Monthly"; however sometimes the
tabs aren't named consistently, for instance, the first tab may be named
"Data- Monthly".

I run a macro that uses this first sheet, and if the name contains typos the
marco errors out. I need VBA code that will NOT try to look for "Sheet 1" or
name of the first sheet, but will simply reference the first Sheet in the
workbook and then name the sheet a name that I give it in the Macro. This way
the sheet name will always be correct when the macro runs. Please advise.

Thanks Much,

 

[Dave Peterson]

#1. Try this:

Application.displayalerts = false
'do your data|text to columns
application.displayalerts = true

#2. It's always the leftmost worksheet in the workbook:

dim MonthlyWks as worksheet
set MonthlyWks = worksheets(1)

 

[Guest]

Dave, Thanks for the good help. Solution 1 worked great

In solution 2, do I use this code then to name the left sheet?
Worksheets(1).Name = "JQA"

I am not sure if this will work. Please advise.

Thanks.

 

[Gord Dibben]

Brent

Scenario #1

Columns("A:A").Select
Application.DisplayAlerts = False
'T to C code here
Application.DisplayAlerts = True


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Sometimes, it's easier to just try it to see if it works for you.

But it sure looks like it would work ok -- well, if there isn't a worksheet
named JQA and the workbook isn't protected.

 

[Guest]

Gord, Thanks for your assistance and post. I should probably clarify my last
post. I was able to get #1 about turning the alerts on and off to work fine.

What I still need assistance w/ is #2 regarding naming the left most sheet
of a workbook.

Dave, I tried:
Worksheets(1).Name = "JQA"
But am getting an error.

Any thoughts?

 

[Dave Peterson]

Is the workbook protected?

Is there an existing sheet with that name?