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 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
