Paul thanks, you helped me a ton. Basically this coupled with an
Outlook macro saved me 2 and a half hours a day in work. Thank you
very much. I can already see this being modified to help in other
tasks around the office!
Regards,
Jason
On Dec 3, 8:28 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> OK... here's what I came up with.
> I'm sure there are MANY other ways of doing this, but this uses some
> concepts that it would be good for you to know.
>
> You described two problems:
> 1) Sometimes, there is a single space after the first character of the
> movement
> type: "D 0970"
> 2) Add a suffix to the sheet name when a duplication occurs.
> So...
> ----------------------------------------------------------------------------------------------
>
> 1) Sometimes, there is a single space after the first character of the
> movement
> type: "D 0970"
>
> ----------------------------------------------------------------------------------------------
>
> When testing for the space, (If (Mid(tMvt, Pos, 1) <> " ") Then) instead of
> exiting
> the loop when you find one, check to see if the space is EARLY in the string.
> If Pos > 3 Then Exit For
> That way, if the string is "D0970", "D 0970", or even "D 0970", it will skip
> the space, but
> continue looking.
> ----------------------------------------------------------------------------------------------
>
> 2) Add a suffix to the sheet name when a duplication occurs.
> ----------------------------------------------------------------------------------------------
>
> If your script attempts to rename a sheet to a name that already exists, it
> will
> cause an error
> message.
> You can turn off this error message with:
> On Error Resume Next
>
> then, you can check to see if an error occurred.
> If all is well, Err.Number = 0, if an error occurs, Err.Number will NOT be 0.
> In this case, Err.Number is 1004 and Err.Description is:
> "Cannot rename a sheet to the same name as another sheet, a referenced object
> library or a workbook referenced by Visual Basic."
>
> The next thing you need to know is that there are 256 characters in the ASCII
> character set (and Extended ASCII)
> (numbered 0-255)
> These can be retreived using the chr() function.
> It happens that:
> The Upper Case letters A-Z are Chr(65-90) M = Chr(77)
> The Lower Case letters a-z are Chr(97-122) m = Chr(109)
>
> So, if you wanted to append the letter "B" to your Movement and Docdate
> string
> for the sheet name, use:
> ActiveSheet.Name = Mvt & " " & DocDate & Chr(66)
>
> But then, if THAT exists, you need to try "C", then "D".. and so on.
> since the ASCII index is numeric, you can use a simple loop:
>
> ' Attempt Letters B-M
> For inx = 66 To 77
> Err.Clear
> ActiveSheet.Name = Mvt & " " & DocDate & Chr(inx)
> If Err.Number = 0 Then Exit For
> Next inx
> ----------------------------------------------------------------------------------------------
>
> With both of these solutions, your macro ends up looking like:
>
> Sub Sheet_Cleanup()
> Dim R, DocDate, Pos, tMvt, Mvt
> Dim inx
> For R = 1 To 100
> If (Left(Cells(R, "A"), 5) = "Date:") Then
> DocDate = Replace(Replace(Replace(Left(Cells(R, "A").Value, 15),
> "Date:",
> ""), " ", ""), "/", "")
> If (R > 1) Then
> Rows("1:" & R - 1).Delete Shift:=xlUp
> Exit For
> End If
> End If
> Next R
> For R = 1 To 100
> If (InStr(1, Cells(R, "A").Value, "Movement") > 0) Then
> Pos = InStr(1, Cells(R, "A").Value, "Movement")
> tMvt = Trim(Mid(Cells(R, "A").Value, Pos + 12, 20))
> Mvt = ""
> For Pos = 1 To Len(tMvt)
> If (Mid(tMvt, Pos, 1) <> " ") Then
> Mvt = Mvt & Mid(tMvt, Pos, 1)
> Else
> If Pos > 3 Then Exit For
> End If
> Next Pos
> End If
> Next R
> ' Upper Case A-Z are Chr(65-90) M = Chr(77)
> ' Lower Case a-z are Chr(97-122) m = Chr(109)
> On Error Resume Next
> Err.Clear
> ActiveSheet.Name = Mvt & " " & DocDate
> If (Err.Number > 0) Then
> ' Attempt Letters B-M
> For inx = 66 To 77
> Err.Clear
> ActiveSheet.Name = Mvt & " " & DocDate & Chr(inx)
> If Err.Number = 0 Then Exit For
> Next inx
> End If
> End Sub
>
> hope this gives you some new ideas to work with!!!
>
> Paul
>
> ----- Original Message ----
> > From: Paul Schreiner <schreiner_p...@att.net>
> > To: excel-macros@googlegroups.com
> > Sent: Fri, December 3, 2010 7:54:24 AM
> > Subject: Re: $$Excel-Macros$$ Sheet rename based on data in sheet & delete
> > row
>
> > Sorry, got distracted.
> > I'll take a look and post something soon.
>
> > Paul
>
> > ----- Original Message ----
> > > From: JsinSk <jsin...@gmail.com>
> > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > Sent: Wed, December 1, 2010 12:24:49 PM
> > > Subject: Re: $$Excel-Macros$$ Sheet rename based on data in sheet & delete
> >row
>
> > > Paul,
>
> > > I have a another other question (excluding the previous one listed
> > > here in previous post).
>
> > > Sometimes the movement number has a single space between the letters
> > > and numbers that make up the flight number. Is there a way to include
> > > this information after the space (Total space will never exceed six
> > > characters/spaces). At current it doesn't include anything after a
> > > space.
>
> > > --
> >>----------------------------------------------------------------------------------
> >-
> > >-
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links :
> > >http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > Like our page on facebook , Just follow below link
> > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > --
> >----------------------------------------------------------------------------------
> >-
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
>
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
