Old topic, new post

[Tony]

To me, our macro is really convoluted and backwards as a way to copy
data. I didn't develop it - but I have to fix the code. I'll try to
explain without going into specific details unless there's no other
way about it.

We import data from a clients provided Excel workbook (roughly 25
sheets) into a single inhouse spreadsheet. The client recently
modified a couple of things in their workbook that broke our VB macro.
Originally, the clients tabs were named (for example) Cover, TOC,
XYZ1, XYZ2, XYZ3, etc.. and recently they have been renamed to ABC
Cover, ABC TOC, ABC XYZ1, ABC XYZ2,... Not a big deal.

What I need is a way for the macro to determine if the workbook data
being imported has the 'Cover' tab or if it's not there to substitute
'ABC Cover' (or vice versa), and related worksheets, etc.. I need the
macro to continue to read the old way plus their new way in one macro
instead of having two seperate macros to read the different tab names.
I would post the code here, but it is in 14 different Modules and I
probably couldn't post enough to be clear.

Respond personally or the list. If you contact me directly, I can
share the code.

TIA

-Tony

 

[Frank Kabel]

Hi Tony
just as a general idea:
- loop through all worksheets (For each sh in worksheets....) and test
if this is a renamed version or not
- set a flag according to your test results (maybe define this ´flag as
a global variable)
- use this flag within your macro to determine if you ahve to use the
old procedure or the new one

 

[Greg Koppel]

Hi Tony,

You don't mention how you are currently searching the sheets, but if you are
using the old tab names, i.e., If ActiveSheet.Name="Cover" then ...
you can easily change it to If Right(ActiveSheet.Name, 5)="Cover" then ...
You might also be able to use the InStr function. If you get a non-zero
return, the old tab name is contained in the new.

HTH, Greg

 

[Bob Phillips]

Tony,

I would suggest you add a new procedure that loops through all worksheets
and either rename them to the new format (or even old) or leave them alone.
In fact, if you change any new form at to the old, you can ignore the rest
of the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ivan F Moala]

Hi Tony
You don't mention if your client is using a template Workbook
ie a workbook that you or they have made up and use to submit the
data to you, and that ths template book is static (same book all the time)
If so then you can change your code to reference the Sheets code name
instead of the sheets name.

eg.

Sheets("xy TOC").Select >>> Sheet1.Select

While in VBA Editor (Alt + F11) view project explororer (Ctrl + R)

You will see the sheet code name >>> Sheet1(xy TOC)

 

[Tony]

Hi Greg,

The macro doesn't really search for the sheet, it selected it with a
statment:

Sheets("Cover").Select

I'm trying to come up with basic logic that checks if 'Cover' is
there. I grabbed J.E. McGimpsey's code and modified it a bit as
follows, but it's not really working very well. It works ok for the
Cover but doesn't append to the other tabs.

Dim name As String
Dim gensitename As String
Dim tabcase As String
Dim wkSht As Worksheet
covername = "Cover"
gensitename = "ABC "

On Error Resume Next
Set wkSht = Worksheets("Cover")
On Error GoTo 0
If Not wkSht Is Nothing Then
tabname = covername 'worksheet exists
Else
tabname = gensitename + covername 'worksheet doesn't
exist
End If


regardless, it appends the variable to the original statement

Sheets(tabname).Select

Where is my thinking crossed at?

 

[Bob Phillips]

Tony,

Did you see my approach, it saves modifying the existing code at all?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Greg Koppel]

Then how about adding some error handling since I assume you get an error
with the new sheet names?

On Error Resume Next
Sheets("Cover").Select
Sheets("ABCCover").Select

HTH, Greg