PDA

View Full Version : MOving and Sorting Data Marco from master sheet



Ste1605
01-22-2014, 02:10 AM
Hey,
I have a tab within a spreadsheet (Raw Data) which I have alot of data I would like to split up. I have 4 other tabs I would like the data split in to. I would also like the data to pasted in colum c on each of the below tabs on my spreadsheet.

Majority Rule Failed
No DV Price
Provider Compare Failed
Single Sourced
I have recorded the below macro however it works but not as I want it to.

ActiveSheet.Paste
Sheets("Raw Data").Select
ActiveSheet.Range("$A$1:$T$14168").AutoFilter Field:=1, Criteria1:= _
"No DV Price"
Range("A1:T14168").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("No DV Price").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Raw Data").Select
Range("A1").Select
ActiveSheet.Range("$A$1:$T$14168").AutoFilter Field:=1, Criteria1:= _
"Provider Compare Failed"
Range("A1:T14168").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Provider Compare Failed").Select
Application.Run "IDC__SheetActivate"
Range("A1").Select
ActiveSheet.Paste
Sheets("Raw Data").Select
Range("A1").Select
ActiveSheet.Range("$A$1:$T$14168").AutoFilter Field:=1, Criteria1:= _
"Single Sourced"
Range("A1:T14168").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Single Sourced").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Control Sheet").Select
Application.Run "IDC__SheetActivate"
End Sub
Help greatly appreciated.

GTO
01-22-2014, 03:14 AM
Hi Ste1605,

First and foremost, welcome to vbaexpress! I am very sure you will be happy you joined here, as there are lots of members who enjoy sharing their knowledge and will somehow 'find the time' to help with anything they know/are experienced at.

As to your current issue, I would suggest posting the workbook, or a sample accurately reflecting your workbook with sensitive data substituted for.

When posting code snippets. please use the Code tags (press the pound icon above the Reply's edit control.

Mark

Ste1605
01-22-2014, 03:40 AM
Hi Ste1605,

First and foremost, welcome to vbaexpress! I am very sure you will be happy you joined here, as there are lots of members who enjoy sharing their knowledge and will somehow 'find the time' to help with anything they know/are experienced at.

As to your current issue, I would suggest posting the workbook, or a sample accurately reflecting your workbook with sensitive data substituted for.

When posting code snippets. please use the Code tags (press the pound icon above the Reply's edit control.

Mark

Thanks for the welcome I will add a snippet of the spreadsheet on to the formum - thanks.

Ste1605
01-22-2014, 03:44 AM
Please see snippet of spread sheet. The rawe data sheet will have lots of lines upwards of 30k

Each tab is in the end result stage also.

BS.Singh
01-27-2014, 01:06 PM
Hi Ste1605,

If I understood your problem correctly then below code should solve your problem:



Sub test()
Dim sh As Worksheet, sh1 As Worksheet
Dim r As Range, C As Range
Dim i As Integer, lr As Integer
Set sh = ThisWorkbook.Sheets("Raw Data")
Set r = sh.Range("A2", sh.Cells(Rows.Count, "A").End(xlUp))
i = 2
For Each C In r

For Each sh1 In ThisWorkbook.Sheets
If C.Value = sh1.Name Then
lr = sh1.Cells(Rows.Count, "C").End(xlUp).Row + 1


sh.Range("A" & i, "T" & i).Copy sh1.Range("C" & lr)
i = i + 1
End If
Next
Next
End Sub

Ste1605
01-30-2014, 10:01 AM
Thank you so much for you response.

I have assigned this code however I am having trouble still with the macro. The macro is not giving me any type of error message it seems to be running however no action is taking place with in my spreadsheet.

It would be great if you could shed some light on this for me.

Kind Regards
S

david000
01-30-2014, 10:42 AM
Thank you so much for you response.

I have assigned this code however I am having trouble still with the macro. The macro is not giving me any type of error message it seems to be running however no action is taking place with in my spreadsheet.

It would be great if you could shed some light on this for me.

Kind Regards
S

Make sure you have a macro enabled worksheet first.

Then unfilter the worksheet Raw Data, delete all the information on all the other worksheets under the headings, run the macro and you will see all the data back under the headings.