Inline.
SteveC wrote:
Fantastic Dave. It works beatifully. Thanks a lot. This solves the core of my problem to format sheets of my workbook into "list" format that Access can use.
My next step is to to figure out how to use ADO to consolidate all my workseets into Access.
Some non-core questions for you and others that are not yet bored...
1)
Could you clarify what you mean by turning off the "on error resume next" lines. Am I to assume that you meant that GoTo 0 will help in the development process of the macro, but once the macro is finalized this line GoTo 0 should be deleted?
I mean that you can turn error checking off with "on error resume next", then do
the process that may cause the error--but turn back error checking as soon as
your done with that statement.
On error resume next
activesheet.pictures("hithere").delete
on error goto 0
If there isn't a picture named "hithere" on the activesheet, then that's ok with
me. I'll just ignore the error. But I want to make sure that I'm still
checking for errors when I do the next thing. So I give error handling back to
excel (on error goto 0).
You'd keep both those lines in your production code.
2)
This workbook has 30 worksheets. 25 have identical formats, 5 of them are all different. As a result, I get a Run-time error '13': Type mismatch, and the debugger highlights 7 lines of code that begins with
ElseIf .Cells(Lrow, "A").Value = "" Or _
.Cells(Lrow, "C").Value = "Volume" Or _ etc...
Should I insert between every of those 7 lines "On Error Resume Next" or some other error coding to avoid this problem?
I'd try to avoid those 5 worksheets. One easy way is to just look at the name
of the worksheet:
Dim sht As Worksheet
for each sht in activeworkbook.worksheets
if lcase(sht.name) = "sheet1" _
or lcase(sht.name) = "sheeta" _
or lcase(sht.name) = "mysheet" _
or lcase(sht.name) = "hithere2" _
or lcase(sht.nane) = "whatshappening" then
'do nothing
else
'do your real code
end if
next sht
You could also use the "select case" structu
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
Select Case LCase(sht.Name)
Case Is = "sheet1", "sheeta", "mysheet", _
"hithere2", "whatshappening"
MsgBox "skip it"
Case Else
MsgBox "do it"
End Select
Next sht
3)
Please note that of course I can just delete these 5 worksheets before the formatting process begins, but then I wouldn't learn more about error coding.
Application.DisplayAlerts = False
Sheets("DontNeedSheet1").Delete
Sheets("DontNeedSheet2").Delete
Application.DisplayAlerts = True
How can I write some code that enacts a command to delete every worksheet name that does not end with the letters "EP"? That would be more fun to do I think.
One way...
dim sht as worksheet
for each sht in activeworkbook.worksheets
if right(lcase(sht.name),2) = "ep" then
'do nothing
else
If ActiveWorkbook.Sheets.Count 1 Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If
end if
next sht
I see that you turned off alerts, deleted the sheet, then turned them back on.
Same as the "on error" stuff!
And if you try to that last sheet, you'll get an error. So you can just check
to see how many sheets are still existing before you try.
Another way is to use Like:
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
If LCase(sht.Name) Like "*ep" Then
'do nothing
Else
If ActiveWorkbook.Sheets.Count 1 Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If
End If
Next sht
4)
I am also wondering how I can duplicate this workbook first, and then have the macro reformat the duplicated workbook? I adjusted the following from a post by Don Guillett and will try to implement it tomorrow.
With ActiveWorkbook
MyWB = .Path.Name
.SaveCopyAs MyWB
.Save
End With
dim mySavedCopy as workbook
dim myFileName as string
myfilename = "c:\mypath\....\whateveryouwanthere.xls"
with activeworkbook
.savecopyas filename:=myfilename
end with
'then just reopen it
set mysavedcopy = workbooks.open(filename:=myfilename)
======
(Not sure why you saved the original in Don's example.)
Thanks again Dave. Thanks again all. I appreciate your help very much.
Regards,
SteveC
Good luck with the ADO stuff. I don't speak the ADO, but there's lots of people
who do who hang around here.
--
Dave Peterson