What you describe is easily done.
However, I wouldn't be a very good teacher if I simply GAVE you the answer!!!
;-)
You've described things nicely.
Now think in terms of how you get Excel to go through the steps.
-----------------------------------------------------------------
1) I need everything above the row that has the date in it deleted
How do you find the row with the date in it?
You begin looking at the first row and move down until you find the date.
For R = 1 to 100
if (left(cells(R,"A"),5) = "Date:") then
End If
next R
Next, how do you delete rows?
I would record a macro in which you delete the rows:
Rows("1:2").Select
Selection.Delete Shift:=xlUp
then, combine it to a single line:
Rows("1:2").Delete Shift:=xlUp
Next, put it into the above macro and replace the ending row with
the row ABOVE the one where you found the date:
(I added the "If (R > 1)" so that if you run it on an already processed sheet,
it won't try to delete a line #0!)
For R = 1 to 100
if (left(cells(R,"A"),5) = "Date:") then
If (R > 1) Then
Rows("1:" & R - 1).Delete Shift:=xlUp
Exit For
End If
End If
next R
Next:
-----------------------------------------------------------------
2) I would like to see if I can get the tab to take the name of the Movement
number & date
-----------------------------------------------------------------
OK, find the Movement Number and Date:
You found the date above, simply store it:
(I collected the left-most 15 characters, removed the "Date:" string and empty
spaces and replaced the "/"s)
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
To find the Movement Number, think in terms of how you do it manually:
You first start at row 1 and move down until you find the row with the word
"Movement",
(use instr() function)
then you remove the part of the row from the beginning of the line to the term
"Movement No:"
(I used Mid())
and remove the leading spaces (I used trim())
Next, collect the characters until you find the first space (I used a For loop):
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
Exit For
End If
Next Pos
End If
Next R
Now you have both the Movement Number (Mvt) and the Document Date (DocDate)
Next, record a macro in which you change the sheet name:
Now the recorded macro uses the current sheet name:
Sheets("2010-11-20_PM-07-54-33_CVG CARG").Name = "test"
but you can replace it with ActiveSheet.Name
So you end up with:
ActiveSheet.Name = Mvt & " " & DocDate
So you end up with:
Sub Macro4()
Dim R, DocDate, Pos, tMvt, Mvt
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
Exit For
End If
Next Pos
End If
Next R
ActiveSheet.Name = Mvt & " " & DocDate
End Sub
Now.. keep in mind that the sheet names you've suggested MUST be unique!
In your sample file, both sheets are dated 20/11/10 with Movement No's: D0970
So it would try to change the two sheet names to the same name, which doesn't
work.
give it a try and see what you can learn!
Paul
>
>
>
>From: J-Sin <jsin...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Wed, November 24, 2010 11:16:22 AM
>Subject: $$Excel-Macros$$ Sheet rename based on data in sheet & delete row
>
>Hello experts,
>
>I have attached a sheet that I've been working on that is part of a multi-step
>task that I have so far managed to cut over an hour a day from the workload.
>
>
>Overview:
>I get roughly 50-80 emails daily with flight information related to my company
>that previously was completed by someone else. I have set up an MS Outlook
>macro
>that exports all the emails into individual text files in a specified folder.
>
>
>I have a macro already loaded in the attached Excel sheet that imports each
>text
>file into it's own sheet in a single workbook.
>Each sheet takes the name of the text file (which derives it's name from the
>email title/date).
>
>
>
>Issues:
>
>I have a couple issues I would like to see if I can assistance with.
>
>1) I need everything above the row that has the date in it deleted, can be
>done
>manually just think there is an easier way.
>2) I would like to see if I can get the tab to take the name of the Movement
>number & date (eg. DO970 20112010)
>
>Other notes:
>
>The date field can move from row 5 to 10 depending on the number of email
>addresses/other data.
>The macro I used to insert all the text files into a sheet creates a new sheet
>(this one renamed) so the original macro is not included. If needed I can
>provide.
>
>Regards,
>
>Jason
>
>--
>----------------------------------------------------------------------------------
>
>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
>
--
----------------------------------------------------------------------------------
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
