Help with creating a macro that will copy and paste data to multiple sheets.

Hello, its my first time posting on here so sorry if i am a bit slow.

A bit of back round, Im looking to create a reporting framework within excel using data exported from my project management software. The exporting of the data is easy as there is a function to do this within the application however what I am struggling to do is to get the data sorted from the master sheet into multiple separate sheets based on text in particular columns.

I have 8 sheets within this wookbook-

1. "Import Data"
2. "NSSR"
3. "Projects"
4. "Un-worked"
5. "Active Inbox"
6. "Dashboard"
7. "Opened this Month"
8. "Closed"

I will export the data into the "Import Data" sheet and from there I am hoping to create a function which will automatically copy and paste the entire row into the relevant sheets based on criteria in certain columns.

For the "NSSR" sheet I would like all the data in the "Import Data" sheet to be copy and pasted into it as long as it says "NSSR" in column G or if it says "Awaiting Functional Approval", "Awaiting Estimate", "Estimate", "Estimate Issued", "Budget Approved", "Active" or "Rejected" in column E.

For the "Project's" sheet I would like all the data in the "Import Data" sheet to be copy and pasted into it as long as it says "Req Brief", "Sponser Approval", "Portfolio Board" or "BTRD" in column G.

For the "Un-worked" sheet I would like all the data in the "Import Data" sheet to be copy and pasted into it as long as it says "Registered" in column E.

For the "Active Inbox" sheet I would like all the data in the "Import Data" sheet to be copy and pasted in as long as it doesn’t say "Closed", "Awaiting Functional Approval", "Awaiting Estimate", "Budget Approved" or "Active" in column E.

The Dashboard tab I can do using the countif function.

For the "Opened this Month" sheet I would like all the data in the "Import Data" sheet to be copy and pasted in regards of what it says in column E or G however the date in column F must be of the current month. (I don’t know if this is possible but it would be fantastic if it is)

For the "Closed" sheet I would like all the data from the "Import Data" sheet copy and pasted in as long as it says "Closed" in column E.

I think that is everything covered, the plan is to have all those functions in code in VBA then create a button which I know how to do then when the data is imported into the workbook I can just press the button and all the data will be sorted into the individual worksheets.

If anyone could help me out with this it would be fantastic, everything I have tried so far has not worked (I even ended up creating a loop virus by accident crashing excel)

Very many thanks

Adam

Maybe this can get you started?

Sub harkin123()
Dim i As Long
For i = ActiveSheet.UsedRange.Rows.count To 2 Step -1
        Select Case Range("G" & i).Value
            Case Is = "NSSR"
            Range("G" & i).EntireRow.Copy Sheets("NSSR").Range("A" & Rows.count).End(3)(2)
            Case Is = "Req Brief", "Sponsor Approval", "Portfolio Board", "BTRD"
            Range("G" & i).EntireRow.Copy Sheets("Projects").Range("A" & Rows.count).End(3)(2)
        End Select
        Select Case Range("E" & i).Value
            Case Is = "Awaiting Functional Approval", "Awaiting Estimate", "Estimate", "Estimate Issued", "Budget Approved", "Active", "Rejected"
                Range("E" & i).EntireRow.Copy Sheets("NSSR").Range("A" & Rows.count).End(3)(2)
            Case Is = "Registered"
                Range("E" & i).EntireRow.Copy Sheets("Un-worked").Range("A" & Rows.count).End(3)(2)
            Case Is = "Registered", "NSSR", "Estimate", "Estimate Issued"
                Range("E" & i).EntireRow.Copy Sheets("Active Inbox").Range("A" & Rows.count).End(3)(2)
            Case Is = "Closed"
                Range("E" & i).EntireRow.Copy Sheets("Closed").Range("A" & Rows.count).End(3)(2)
         End Select
 If Month(Range("F" & i)) = Month(Now) Then
         Range("F" & i).EntireRow.Copy Sheets("Opened this Month").Range("A" & Rows.count).End(3)(2)
 End If
 Next i
End Sub

You would have too work on what should go on Sheet Active Inbox vrs what doesn't.

Hi John, thank you so much for helping me with this. I have ran the macro and it has come up with "runtime error 9" "sunscript out of range". Any idea what may have caused this?

Thanks

Adam

Adam:

You need to check your Sheet Names. Which line is highlighted

Sorted them out now, thank you. Ive ran it again and this time its come up with "type mismatch". One row has been copied into each of the sheets from the master sheet but there is nothing else been copied in?

thank you

Can you attach a sample file so we can see what may be causing the problem?

To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Here you go mate. Thank you. I Was also wondering if its possible to get the data to paste below the titles in the columns you will see in each sheet rather thsn deleteling the columnsas it does now?

many thanks

Morning John,

Any luck with the sample I sent you? I've been trying to fix the error but to no avail. Do you need me to send you anything else?

Many thanks

Adam

Adam. I'm sorry when I created a workbook and tested the code provided seemed to do what you required. However, I can't get it too work on your sample. I've tried three different loops. I don't know why it doesn't work. When I look at the code, too me it should produce the results? I can't figure this one out. Maybe someone else may have some ideas.

Oh dear, no idea what could cause that. Maybe theres settings of some sort stopping the code?

John,
I think the code works, but need a bit of tweaking.
You are copying an entire row and pasting it in to column A, while column A is blank, so the code picks the right row, but it keeps overwriting the new copy in old one. I am only referring to the attached. The headings are in column B-H, while you can only copy an entire row in column A, but the next empty row in column A is always be 1.
Perhaps resizing as oppose to entire row should work.

Range("E" & i).Resize(, 8).Copy Sheets("Closed").Range("B" & Rows.Count).End(3)(2)

Hi AB,

Thanks for taking a look at it for me. I have made the changes you suggested above however there is still an error code apprearing even though It is coping more of the data and into the correct place. It does seem to be just copying a couple of columns aswell not the whole row.

My brain is fried from this, would you be able to take a look for me and see if theres anything that need to be changed?

Than you so much

Adam

Originally Posted by AB33

John,
I think the code works, but need a bit of tweaking.
You are copying an entire row and pasting it in to column A, while column A is blank, so the code picks the right row, but it keeps overwriting the new copy in old one. I am only referring to the attached. The headings are in column B-H, while you can only copy an entire row in column A, but the next empty row in column A is always be 1.
Perhaps resizing as oppose to entire row should work.

Range("E" & i).Resize(, 8).Copy Sheets("Closed").Range("B" & Rows.Count).End(3)(2)

Great observation AB33. I was really stumped on this one.

As an option to John's code, though I have not done column F.

John,
You are not the first one; I have been through it and still does it occasionally.

AB, thats working now, thank you so much. I think ive got some tewaking to do but nearly there.

John aswell thanks for much for your help earlier on. Youve both been amazing.

many thanks

adam

I have already had some fantastic help on thisfurthuer up in this thread. However now I'm testing it I am starting to find little problems with the sorting.

Here are the problems I am having;

1. The 'Inbox' sheet is not containing the correct data (seems to be missing a lot even when the code looks fine to me). Below are what I am hoping to achieve.
2. I am yet to get the 'Opened this Month' sheet to work (problem with the code? I cant work it out).

Apart from these It seems to be working really well, If someone could help me to resolve these problems that would be amazing! I have attached the doc for investigation.

Here is what I am hoping to acheieve;

I have 8 sheets in total:


1. "Import Data"
2. "NSSR"
3. "Projects"
4. "Un-worked"
5. "Active Inbox"
6. "Dashboard"
7. "Opened this Month"
8. "Closed"

I will export the data into the "Import Data" sheet and from there I am hoping to create a function which will automatically copy and paste the entire row into the relevant sheets based on criteria in certain columns.

For the "NSSR" sheet I would like all the data in the "Import Data" sheet to be copy and pasted into it as long as it says "NSSR" in column G or if it says "Awaiting Functional Approval", "Awaiting Estimate", "Estimate", "Estimate Issued", "Budget Approved", "Active" or "Rejected" in column E.

For the "Project's" sheet I would like all the data in the "Import Data" sheet to be copy and pasted into it as long as it says "Req Brief", "Sponser Approval", "Portfolio Board" or "BTRD" in column G.

For the "Registered" sheet I would like all the data in the "Import Data" sheet to be copy and pasted into it as long as it says "Registered" in column E.

For the "Inbox" sheet I would like all the data in the "Import Data" sheet to be copy and pasted in as long as it doesn’t say "Closed", "Awaiting Functional Approval", "Awaiting Estimate", "Budget Approved" or "Active" in column E. So it must say either "In Progress", "Registered", "Estimated", "Estimate Issued" or "Rejected" in column E.

The Dashboard tab I have done using the countif function.

For the "Opened this Month" sheet I would like all the data in the "Import Data" sheet to be copy and pasted in regards of what it says in column E or G however the date in column F must be of the current month. (I don’t know if this is possible but it would be fantastic if it is). I have not been able to get this to work yet so have deactivated the code in vba.

For the "Closed" sheet I would like all the data from the "Import Data" sheet copy and pasted in as long as it says "Closed" in column E.

I think that is everything covered, the plan is to have all those functions in code in VBA then use the buttons to activate them.
If anyone could help me out with this it would be fantastic, I have been scratching my head over how to try and get rid of the kinks.


Very many thanks

Adam