Macro to move data into 3 diff worksheets

August 24, 2011 at 09:19:08
Specs: Windows XP

I have a master sheet that captures cosolidated processes in all departments, sheet name is "Master Data". Data is from column B to col L.

Col B to Col G contains details of the processes such as Group, cost center, department name, process name, document number alloted to process, etc.

On col H, we update either of the three status of these process as UNDER REVIEW, REVIEWED and TERMINATED.

On col L, we update the Persons name responsible for the department.

I'm looking for a macro that would create 3 worksheets "UNDER REVIEW", "REVIEWED" and "TERMINATED" as per Col H. Once worksheets are created, copy and move the entire rows based on col H Status from Master Data to respective tabs created as"UNDER REVIEW", "REVIEWED" and "TERMINATED"

Could someone please assist me with a macro to do this.

Also the data that is copied over from Master Data should be pasted in the order appearing in Master data sheet.

Please see example below:

Worksheet: Master Data

Col. F	                  Col.H	                  Col L
Document No.	  Status	                  Person Responsible

S1	               Reviewed	                    S
S2	              Reviewed	                    S
S3	              Terminated	                    S
S4	             Under Review	                    S
S5	             Under Review	                    S
M1	             Reviewed	                    M
M2	            Terminated	                    M
M3	            Under Review	                    M
M4	            Under Review	                   M
K1	            Reviewed	                   K

After running the macro:

Create Worksheet "Reviewed" and copy and paste the following data along with entire rows

S1	Reviewed	S
S2	Reviewed	S
M1	Reviewed  M
K1	Reviewed	K

Worksheet "Under Review":
S4	Under Review	S
S5	Under Review	S
M3	Under Review	M
M4	Under Review	M

Worksheet "Terminated"

S3	Terminated	S
M2	Terminated	M

Let me know if you need additional information.

Many thanks for any help on this.

See More: Macro to move data into 3 diff worksheets

Report •

August 25, 2011 at 09:25:36

Hi Derbydad,

I used one of the codes provided by you to some user after making small changes. I created 3 worksheets before running the macro. Below is the code for your reference:

Option Explicit
Sub UpdateTotals()
Dim catSht As String
Dim myTrans, lastTrans, transRow As Integer
'Find last Transaction in Register sheet, Column H
  lastTrans = Sheets("Master Data").Range("H" & Rows.Count).End(xlUp).Row
'Loop through Column H, copying B:L to the appropriate category sheet
  For myTrans = 3 To lastTrans
'Determine Category Sheet name
   catSht = Sheets("Master Data").Range("H" & myTrans).Value
'Find last row in Category Sheet
    transRow = Sheets(catSht).Range("H" & Rows.Count).End(xlUp).Row + 1
'Skip Row 2
     If transRow = 2 Then transRow = 3
'Copy line to Category Sheet
      Sheets("Master Data").Range("B" & myTrans & ":L" & myTrans).Copy _
        Destination:=Sheets(catSht).Range("B" & transRow)
End Sub

This works fine, I'll be running this macro once in a month. however I think I would need to delete the existing data in these 3 worksheets before running the macro, correct? Because we would be adding additional processes in the master data sheet under different departments and I'm not sure if this code will handle the updates.

Many thanks

Report •
Related Solutions

Ask Question