Solved: Cut and paste into new sheet

[john3j]

Urgent!

Hey guys im trying to meet a deadline and I would appreciate and response. Here is the deal. I am trying to take date from one sheet and seperate it into different premade sheets based on what the first three letters of the ID are.

I am pasting data into the main data sheet once a month, and each month, the data needs to be sorted and pasted onto the sheet for that month. I have the following sheets:

MainSheet, September, October, December, January, February, March, April, May, June, July, and August. I manually paste data into the main sheet each month. I need the data sorted by division of our branch. The branches are XPT, XPS, XPR, XPQ, and XPP. In column 2 of the main sheet defines which division it is. This data will need to be sorted into different sections on the sheet depending on the division so that it can be totaled, and it will need to prompt me or let me choose which month sheet to paste the data onto. If this sounds crazy confusing, please feel free to contact me on MSN messenger john3j@hotmail.com. Any help would be greatly appreciated. Please Help?!

Thanks,
John:dunno

[mdmackillop]

Hi John,
Can you make up a sample workbook with a realistic layout. Use Manage Attachments in the Go Advanced reply section to post. Add any explanatory comments etc.
Regards
MD

[john3j]

Ok, here we go. What I am trying to do is kind of hard. I have this database that pulls this information from Sharepoint. I am trying to create a template to run each month to track this info. The first sheet is where the data comes from. The Range we need to search from this sheet is C2:C1000. For each cell in this range that meets the criteria for the different division, it needs to be pasted into the bottom of the respectful division sheet. You will see at the top of each sheet i want to use countif functions to count the yes's and no's. So, on the main sheet, each month i will go and paste the data into the first sheet. I then want to be able to execute this code that will put the data in the respective data sheet, but i dont know how i would be able to track it by month. I am tracking first past yeild and i need charts to track it, if you find a better way, i would appreciate it...but the criteria needs to be there that is in the other data sheet. Please help!

[mdmackillop]

There is no MainSheet in your posted workbook.

[john3j]

The main sheet is the first one name "First Pass Yield Branch". Any info would help. Even if I just had a code to with a select case for the different branches...from the main page, in column C which is the division column, depending on what division it is, cut and paste that row into the sheet titled that particular division. It would be nice if it would paste all of the rows into and area A10:H500 on each respective sheet. Also, how would I seperate the data depending on what month I run this? I started by creating a sheet for each month, but that data will just run into the FPY Data Sheet. I appreciate the help!

John

[mdmackillop]

Hi John,
Without sample data, I don't know where to start.
Regards
MD

[john3j]

hey i updated the spreadsheet and added some bogus sample data. Remember that i want to go thru column c of the first sheet and look for the divisions and cut and paste each row into the respective sheet. Also if you have any ideas on how i would track data from month to month without over writing the data from other sheets, rather than the first sheet, each month. thanks again for your help!

[mdmackillop]

Hi John,
Moving data around is not difficult if the reference is clear. However, you refer to things like Respective Data Sheets. Please be clear what these are. You know your terminology, but I don't. I'm not at all clear what gets sorted for allocations to months. If you add data to secondary sheets with comments, this would assist.
Anyway, here is the first allocation.

[john3j]

Ok, sorry for all of the confusion. Lets see if this helps. From the First Pass Yield Branch sheet...sort the following.

For every row on that page, if column 3 contains "XPP" then cut the row and paste it onto the sheet titled "XPP" into the next blank row, starting with row 10 of the XPP sheet.

For every row on that page, if column 3 contains "XPQ" then cut the row and paste it onto the sheet titled "XPQ" into the next blank row, starting with row 10 of the XPQ sheet.

For every row on that page, if column 3 contains "XPR" then cut the row and paste it onto the sheet titled "XPR" into the next blank row, starting with row 10 of the XPR sheet.

For every row on that page, if column 3 contains "XPS" then cut the row and paste it onto the sheet titled "XPS" into the next blank row, starting with row 10 of the XPS sheet.

For every row on that page, if column 3 contains "XPT" then cut the row and paste it onto the sheet titled "XPT" into the next blank row, starting with row 10 of the XPT sheet.

For every row on that page, if column 3 contains "XPU" then cut the row and paste it onto the sheet titled "XPU" into the next blank row, starting with row 10 of the XPU sheet.

From the "First Pass Yield Branch" sheet, there should never be more than 1000 rows of data, so the range you would be searching for XPP,XPQ, XPR, XPS, XPT, XPT, and XPU would be C2:C1000.

How does the Range work? Is there a different way of calling a range. For instance, i was looking at other code submitted and I saw where someone called a range a specific number like C65789 instead of a range in the format of C2:C1000. I dont know where those number come from. Once again, I appreciate the time and effort.

If you need anymore info, please let me know!
Thanks again for your help, you are a lifesaver!

[john3j]

I forgot to mention, each time the code is executed, or like in your example, if the ID Allocate Button is clicked, it should clear the content from Row 10 to Row 1000 and paste the data back at row ten, basically clearing the contents and repasting. The example you created is amazing, but I would like to know what the code actually means. If you could notate the code that would be great, but dont worry about it if you dont have the time. If I understand the code, I can learn it and share it with other people! Thanks!

[mdmackillop]

Your header formulae make reference as shown. If you clear First Pass as requested, this formula will fail. It refers to only forty lines of data, which does not appear to make sense in the context of 1000 rows of data.
=COUNTIF('First Pass Yield Branch'!E3:E44, "Yes")

Sub ID_Allocate()
Dim Rng As Range
Dim Cel As Range
Dim tgt As Range
Dim MainSheet As Worksheet
Dim ws As Worksheet
Dim arr, a

'Clear sheets prior to pasting data
arr = Array("XPP", "XPQ", "XPR", "XPS", "XPT", "XPU")
For Each a In arr
Sheets(a).Range("A10:H1000").ClearContents
Next

Set MainSheet = Sheets("First Pass Yield Branch")
With MainSheet
'Set Division range
Set Rng = Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
'Loop through each cel
For Each Cel In Rng
'Determine target sheet
Set ws = Sheets(Cel.Value)
'Check for next vacant cell
Set tgt = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
'Copy and paste data
Cel.Offset(, -2).Resize(, 8).Copy tgt
Next
End With

'Clear data
Rng.Offset(, -2).Resize(, 8).ClearContents

End Sub

[john3j]

You are correct, when i threw it together i only selected 40 rows for the CountIf function. Good news is that i want to get rid of that function and use it on each page XPP, XPQ, XPR, etc to total each division. Then on my total sheet i will roll them all into one. The question is that if I use a countif function on these pages, how can I get them to update onto the FPY Data Sheet, depending on the month that I am im? I need to be able to copy the countif value from each division page (XPP,XPQ,XPR,XPS,XPT,XPU) and load into the FPY Data sheet, depending on what month it is when I run the code. Let me know if you dont understand what I am saying. I had created a new sheet for each month, and i was going to copy and paste the values into the month that it currently is, that way i can create a chart that shows First Pass Yield over the current Fiscal Year. If we can automate this so that it will update into the FPY Data Sheet, i can get rid on the sheets that were create for each month. Thanks man

[mdmackillop]

So in XPP, what formula should be in cell B2?

[john3j]

B2: =COUNTIF(E10:E1000, "Yes")
B3: =COUNTIF(E10:E1000, "No")
C2: =COUNTIF(G10:G1000, "Yes")
C3: =COUNTIF(G10:G1000, "No")

Keep in mind that this is for XPP, but it is going to be the same thing for the different divisions.

[mdmackillop]

The question is that if I use a countif function on these pages, how can I get them to update onto the FPY Data Sheet, depending on the month that I am im? I need to be able to copy the countif value from each division page (XPP,XPQ,XPR,XPS,XPT,XPU) and load into the FPY Data sheet, depending on what month it is when I run the code.
Lost again!
Which countif value? where does it go? What is the layout that accommodates 6 different sheets data?

[john3j]

I will update the spreadsheet with the new values and upload it for you tonight. Talk to you then!

[john3j]

Ok here is what I would like to do. Lets say the day i click the button to sort the data located on the First Pass Yield Branch sheet and the day i clicked in was in the month of May. I would like it to take the data from Branch FPY sheet and paste it into FPY Data Sheet. Here is where things need to go. We will still say that the month is May just for example.

Branch FPY B2 value ---> FPY Data Sheet B9
Branch FPY B3 value ---> FPY Data Sheet C9
Branch FPY C2 value ---> FPY Data Sheet D9
Branch FPY C3 value ---> FPY Data Sheet E9
FPY Data Sheet B20 value ---> FPY Data Sheet F9

Now lets say the month in which we click the button is August:

Branch FPY B2 value ---> FPY Data Sheet B12
Branch FPY B3 value ---> FPY Data Sheet C12
Branch FPY C2 value ---> FPY Data Sheet D12
Branch FPY C3 value ---> FPY Data Sheet E12
FPY Data Sheet B20 value ---> FPY Data Sheet F12

I need it to do it for each month. I have formatted everything else so you shouldnt have to worry about formulas not matching up or anything. I have attached the file again. After this we should be good to go! I took out the test data so if you need more test data let me know. everything else works like a charm.

[mdmackillop]

Add this before End Sub
'copy Branch FPY data
Set ws = Sheets("FPY Data Sheet")
'Get row corresponding to current month
Mth = ws.Columns(1).Find(Format(Date, "mmmm")).Row
'Write the values to FPY Data Sheet
With Sheets("Branch FPY")
ws.Cells(Mth, 2).Value = .Range("B2").Value
ws.Cells(Mth, 3).Value = .Range("B3").Value
ws.Cells(Mth, 4).Value = .Range("C2").Value
ws.Cells(Mth, 5).Value = .Range("C3").Value
End With

[john3j]

hey man the code worked for me yesterday. I added what you provided for me and now every time i click the button it gives me a "subscript out of range" error and will not sort anything. If we can get this straightened out we will be good. I really do appreciate all of your help! I am attaching the file after adding the code. Also, I added more test data. Let me know

[mdmackillop]

You have put the branch codes in Col D, and Col C is empty.

[john3j]

Hey man I got to work today and realized that I should have been tracking by month for each division. I went thru and added the data sheets for everything and even inserted the charts. You know how you added code to make it so that when you click the button and it sorts the data, it goes thru and puts the value in the data table depending on what month it is? Well I should have done that for each page. Lets say we are working with "XPP". We would need to do the following:

Value of B2 needs to go into B8 thru B19, depending on what month it is when you click the sort button.

Value of B3 needs to go into C8 thru C19, depending on what month it is when you click the sort button.

Value of C2 needs to go into D8 thru D19, depending on what month it is when you click the sort button.

Value of C3 needs to go into E8 thru E19, depending on what month it is when you click the sort button.

Value of D3 needs to go into F8 thru F19, depending on what month it is when you click the sort button.

This needs to be done for "XPP", "XPQ", "XPR", "XPS", "XPT", and "XPU".
This does not need to be done for the sheet "FPY Data Sheet" because I am going to make everything a cumulative of all of the other sheets. I have already formatted everything else. Please help me oh wise wizard!

[mdmackillop]

Revised

[john3j]

hey man it works perfectly! you are a lifesaver. hey as of now, i have a database that calls data from Sharepoint and it populates into an excel file. From there I am opening the file, copying the data in A2:H1000 and just pasting it into the first sheet of the project we were working on. Is there anyway to automate this? I have tried doing the import data wizard but it doesnt do anything. Any ideas? I dont know if I can pull it straight from my database or if I would have to somehow have it copy the data from the output file. This is the last thing i swear, then it will do everything i want it to.

[mdmackillop]

Sorry,
I've never worked with Sharepoint. Best to post this as a new question for fresh input.