This directed to John Wilson/or anyone else

  • Thread starter Thread starter Radio848
  • Start date Start date
R

Radio848

Hi Guys & Gals:
John provided me with the enclosed macro, (I have made modifications, so the
problem isn't his fault)
Problem: I can only get the enclosed to work on the first & last page.
There are a total of 42 pages. I've tried doing the "First Page" - "Last
Page", routine and that didn't work.
The enclosed is supposed to be put in "This Workbook" module. It needs to
make the date/time entry on each page at the given offset address.

What am I doing wrong?

Thanks!!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Left(ActiveSheet.Name, 42) = "4.04" Or Left(ActiveSheet.Name, 42) =
"3.07" Then
If Not Intersect(Target, Range("i8:i46")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Value <> "" Then
If Target.Offset(0, 5) = "" Then
Target.Offset(0, 5) = Now
End If
Else
Target.Offset(0, 5) = ""
End If
End If
End If
End Sub
 
I don't understand what you are really trying to do. But, the first thing
you need to do is take a look at the LEFT function..
 
Wayne,

Not sure what you changed on your original workbook.
I do remember that I had originally set this up to work
on sheets starting with 19 & 20 as you had sheets named
as years (1999, 2000, 2001, etc.) and not on any other sheets.

Coding for that was as follows:
If Left(ActiveSheet.Name, 2) = "19" Or Left(ActiveSheet.Name, 2) = "20" Then

What did you change your sheet names to and if this is a different workbook
that the original one that I helped you with, what are you trying to make it
do??

John
 
Wayne,

From your original post in this thread....
If Left(ActiveSheet.Name, 42) = "4.04"
is looking at the leftmost 42 characters of the sheet names???
You could use the "MID" function to try to find the "04" but it
might be easier to do this another way. Remembering how your
other workbook was set up, you only had a couple of sheets named
differently than your data sheets. You could use something like:
If Activesheet.Name = "Database" OR Activesheet.Name = "abc" Then
Exit Sub
Else
' place your code here
End If

The above would let the macro run on every sheet except "Database"
and "abc" (you can add more "OR"'s if necessary).

John
 
Thank you once again John..

Radio848 said:
John: Hello;
The macro is unchanged, with the exception that there are now 42 pages and
the sheet tabs are named "4.04, 5.04, 6.04" etc. The tab names reflect
month.year. This is a different workbook. Entitled "Electric" And is
covers a period of three years.
I realized that I would have to adjust the tabs names in the macro and the
number of pages, but I failed .. For some reason the macro is forcing me to
name each page, in groups of two.
Is that clear?

make so 42)
Click to expand...
 
Back
Top