Sorry, but i could not find any way of attaching excel in my mails
here. Could some one please let me know how I should go abot that.
Many thanks.
Varun
On Oct 23, 6:24 pm, "dguillett1" <dguille...@gmail.com> wrote:
> Best to ATTACH your file. Code could be simplified. ie:
> last = Cells(Rows.Count, "B").End(xlUp).Row
> For i = last To 1 Step -1
> If (Cells(i, "B").Value) = "ABC" Then
> Cells(i, "A").EntireRow.Delete
> End If
> If (Cells(i, "B").Value) = "XYZ" Then
> Cells(i, "A").EntireRow.Delete
> End If
> If (Cells(i, "B").Value) = "Res" Then
> Cells(i, "A").EntireRow.Delete
> End If
> Next i
>
> could be
>
> For i = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
> Select Case UCase(Cells(i, "B"))
> Case Is = "ABC", "XYZ", "RES": Rows(i).Delete
> End Select
> Next i
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>
>
> -----Original Message-----
> From: Varun
> Sent: Sunday, October 23, 2011 3:43 AM
> To: MS EXCEL AND VBA MACROS
> Subject: $$Excel-Macros$$ Need a macro to update data from raw file to
> individual sheets of master
>
> Hi Everyone,
>
> I need a code for the following steps to be enacted
>
> 1. Save a backup file with the name counts - date.xls (this will be a
> daily file)
> 2. remove rows with name (column B) matching with name in to be
> removed sheet
> 3. copy value in name field and open the corusponding sheet in the
> master
> 4. once the sheet is active, go to the last row insert current date in
> left most collumn and paste the row from raw to master (from Column B)
> 5. copy the formula in the remailing columns in that sheet from above
> 6. once updated give a msg = this work is now complete
>
> Conditions -
> 1. If the sheet does not exist in the master then give an inputbox
> informing that we have a new name and asking if a new sheet should be
> created or should this be merged with another existing sheet - if user
> says yes to new sheet then create a new one with the same headers/
> formatting and formulas as the others
> 2. In the end in the msg box (this work is now complete) it should
> contain a summary of unusual events i.e.
> A. New name (sheet created)
> B. New name (merged with existing sheet)
> C. Count for particular name in Column C2 greater than 50 (Name-
> column B and count - C2 detail)
>
> I tried my hand on this but got stuck half way. i was not able to find
> a way to put condition 3 in the code.
>
> This is really urgent....I would be really really really greatful if
> someone can help me out here.
>
> Thanks a lot
>
> I have attached a sample file for test and master with this post along
> with my code.
>
> Raw file:
> ID NAME C1 C2 C3 C4 C5 C6 total
> 6 ABC 799561 56 345345 234 243 234 1145673
> 3089 DEF 17 0 0 0 345 0 362
> 47 GHI 22536 123 0 0 234 2 22895
> 6895 XYZ 31 0 0 0 453 0 484
> 6749 NFF 5 65 1 15 5 0 91
> 74 IJK 122558 511 1 68 11 0 123149
> 75 KLM 6597 21 0 5 13 0 6636
> 42 NOP 69969 123 3 12 234 0 70341
> 103 MON 566278 234 2 17 23 0 566554
> 76 NND 58586 234 0 23 432 0 59275
> 31 PLQ 4283 0 0 0 54 0 4337
> 6325 RST 7 0 0 0 22 0 29
> 5926 DHE 34 0 0 0 24 0 58
> 6781 UUT 3 55 23 6 1 0 88
> 5237 NIQ 20100 41 64 6 817 0 21028
> 101 IJP 8063 2 0 0 65 0 8130
>
> To be removed
> ID NAME
> 6 ABC
> 6895 XYZ
> 345 RES
> 101 IJP
>
> Master
> Record Date ID NAME C1 C2 C3 C4 C5 C6 total F1 F2 F3 F4 F5 FW1 FW2 FW3
> FW4
> 3-Jan-11 3089 DEF 889 0 15 0 2 0 906 906 889 2 15 1795
> 891 17 1810 2686
>
> (similar sheet for all the other values in name column)
>
> Code:
> Sub Counts_upload()
> '
> ' Counts_upload Macro
> ' This macro is used to load running counts from raw to master
> workbook
> ' Keyboard Shortcut: Ctrl+Shift+A
> Workbooks("test").Sheets("raw").Select
> Range("A1").Select
> Dim i As Integer
> last = Cells(Rows.Count, "B").End(xlUp).Row
> For i = last To 1 Step -1
> If (Cells(i, "B").Value) = "ABC" Then
> Cells(i, "A").EntireRow.Delete
> End If
> If (Cells(i, "B").Value) = "XYZ" Then
> Cells(i, "A").EntireRow.Delete
> End If
> If (Cells(i, "B").Value) = "Res" Then
> Cells(i, "A").EntireRow.Delete
> End If
> Next i
> ChDir "C:\Desktop\counts"
> Dim flname As Long
> ActiveWorkbook.SaveCopyAs "C:\Desktop\counts\counts - " &
> Format(Date, "dd-mmm-yy") & ".xls"
> Workbooks("test").Sheets("raw").Select
> Dim k As Integer
> Dim wrk As Worksheet
> Dim n As Integer
> Dim countnonblank As Integer
> Dim myRange As Range
> Set myRange = Columns("B:B")
> countnonblank = Application.WorksheetFunction.CountA(myRange)
> n = countnonblank
> For k = 2 To n Step 1
> wrk = Worksheets("raw").Range("B" & k).Value
> Worksheets("raw").Cells(k, "A").EntireRow.Copy
> If k = "" Then
> Exit Sub
> End If
> Worksheets(wrk).Range("A1").selct
> activecell.End(xlDown).Select
> activecell.Offset(1, 0).Select
> activecell.Value = Date
> activecell.Offset(0, 1).Select
> activecell.PasteSpecial
> Next k
> End Sub
>
> --
> ----------------------------------------------------------------------------------
> 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
> linkhttp://www.facebook.com/discussexcel- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
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/discussexcel
