Re: $$Excel-Macros$$ Need a macro to update data from raw file to individual sheets of master

Sun, 23 Oct 2011 06:24:53 -0700 

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

Reply via email to