We help IT Professionals succeed at work.

vba

Medium Priority
397 Views
Last Modified: 2012-05-12
I need a macro code that will look for a row that has an specific name such as sample and selects/copies that entire row all the way from sample to a point that there would be a new specific row such as sample2. So basically I need a code that would copy data between sample (including sample row) until sample2(excluding sample2) and execute these info to the beginning of a seperate sheet called(sheet5).
Comment
Watch Question

Commented:
have a look at this :

http://www.ozgrid.com/forum/showthread.php?t=58648

quite useful

Author

Commented:
Thank you for your comment. But since it is urgent for me I would appreciate it if you can help me with the code. I would love to research this on my own but I need to figure this out asap :)
CERTIFIED EXPERT

Commented:
Does this have to be a macro or are you open to a formula as well? Can you also post a sample workbook with a few lines of data which reasonable represents your data?

Author

Commented:
Here you go. I hope this make it more clear.
 Help
CERTIFIED EXPERT

Commented:
A sample file would have been more helpful. Now I would have to ask more questions.

How do I know which is the next "Sample"?
    Would it have the word sample in it?
    Would it be the only cell on that row?
    would it be colored green?

Author

Commented:
How do I know which is the next "Sample"?
In sheet5(the destination tab) at the beginning it would have the sample1 (where the data needs to be generated below that row) as default. Then the next sample name which is sample2 needs to be generated after the last row of data that was copied below sample1. So if there needs to be 3 rows of data copied below sample1 in sheet5. Then a row needs to be inserted at row 7 which is a row below the last row of data. So the next sample needs to be named "sample2".

    Would it have the word sample in it?
Yes, It will always have the word sample on Sheet1 (where the data is) and sheet 5 (the destination tab where the info needs to be copied)

    Would it be the only cell on that row?

No, after copying the info for sample 1 then it needs to generate a new cell called sample2 right after the last data copied for sample1.

    would it be colored green?  No I just wanted to mark it green to make it more clear.
CERTIFIED EXPERT
Top Expert 2014

Commented:
@pauledwardian

How is this question different from or related to your other question?
http:/Q_27432306.html

Author

Commented:
Thanks aikimark. It is quite similar but I cannot figure it out. Would you please assist on this?
I have attached a sample workbook.

 Main-Sample.xls

Author

Commented:
Would someone please help me out with this. I would really appreciate it :)

Author

Commented:
OK I found the code I needed but I need help to edit this code.
I need this code to do the same operation for two other row called sample4 and sample5.
Also, when the code copies the rows into sheet6 for sample4 and sample5, I need it to paste those information right after the data that was inserted for sample1 & 2. So that way it does not over write those rows. Please Help!
Sub CopyData()
 Set srcSht = Sheets("sheet1")
 Set dstSht = Sheets("sheet6")
 Dim lastrow As Long
Dim StRow As Range
Dim EndRow As Range
 Set StRow = srcSht.Range("A:A").Find("sample1", LookIn:=xlValues, lookat:=xlWhole)
Set EndRow = srcSht.Range("A:A").Find("sample2", LookIn:=xlValues, lookat:=xlWhole)
If StRow Is Nothing Or EndRow Is Nothing Then
MsgBox "Range Not Found"
 Exit Sub
End If
 lastrow = dstSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
 srcSht.Rows(StRow.Row & ":" & EndRow.Row).Copy Destination:=dstSht.Range("A" & lastrow)
 End Sub

Open in new window

gowflowPartner
CERTIFIED EXPERT

Commented:
Hi Paul
I am a bit confused with what you need here.
Do you want to copy Sample1,Sample2,Sample3 etc .. all the sample and their data that is in Sheet1 under their coresponding sample in sheet 6 is this what you want ?
gowlfow

Author

Commented:
Thanks alot gowflow for your comment. Basically I need to copy the row on sheet1 that is called sample1 all the way until the next filled row. So in my excel sheet the sample one is located on A2. So I need the code to find smple1 in sheet1 and selects all the rows below sample1 until the sample2 row and paste it to sheet6 and include its title (sample1).
Also, it needs to copy the same thing for sample3 all the way to sample4.
Please look at my attachment.
I marked the rows that need to be copied in yellow and blank spaces as green.
Note: The number of rows for sample 1, 2 etc will increase or decrease depending on the amount of rows that they will include.

Paul Main-Sample.xls
gowflowPartner
CERTIFIED EXPERT

Commented:
So only copy Sample1 and Sample3 to sheet6 ? I see in the later comments you need the smae for Sample4 and Sample5 !!!

An other quesiton when we copy to Sheet6 is sheet6 empty or it has data that we need to locate Sample1 there and put the rows to be copied at the end of the existing ones and so on for the rest ???
gowflow
CERTIFIED EXPERT
Top Expert 2014

Commented:
@gowflow

Your multiple question marks and exclamation marks indicate that you are probably in the same state of mind that I attained when answering a related pauledwardian question.  Unfortunately, these questions were not created with the help of the Ask A Related Question link, so you will need to visit the OP's member profile and visit the question links.

pauledwardian is asking for partial solutions and plans to combine these into a complete solution.  You are missing that context.
gowflowPartner
CERTIFIED EXPERT

Commented:
aikimark akthough I appreciate your comments however do not see what it contribute in finding a solution to the asker.
gowflow
CERTIFIED EXPERT
Top Expert 2014

Commented:
@gowflow

Normally, we require context in order to solve a problem properly and completely.  I wanted to let you know that you are seeing part of the problem and its context.

Like you, I suggested a solution that offered to move all the rows and all associated headers that met the selection criterion.  Those comments enlightened me to the partitioned nature of these questions.
gowflowPartner
CERTIFIED EXPERT

Commented:
I there anyhting wrong in asking sevral individual questions that could be part of a single project ? Maybe I am not geting correctly what you are trying to say. Can you be pls simpler in posting your comment or conveying your thought as I am a bit confused as to what you are trying to say. Thank you.
gowflow
CERTIFIED EXPERT
Top Expert 2014

Commented:
>>I there anyhting wrong in asking sevral individual questions...?

It isn't against any EE Terms of Use conditions.  So no (in that respect).

However, these Sample1 and Sample2 sections each have their own EE questions.  The code from the Sample1 answer is supposed to put "Sample2" text into a cell.  Then some other question's code will process the data with the assumption that "Sample2" exists in Sheet6.  Rather than obtaining a consistent and integrated solution, the OP will get solution(s) that
* may not work as intended
* require the OP to integrate the code
* will probably not be as efficient as it could be

=============
In one of my classes, a fairly famous Comp Sci professor had us code separate (encapsulated) routines as teams.  Even his specifications had to be tweaked/amended.  While it was a good exercise as a CS student, I don't see any benefit to such partitioning in this context.  More points aren't always a good thing.

Author

Commented:
aikimark,

I really tried to respectfully respond to all your comments and appreciated every single part that you helped me on the solutions.
It is always nice to guide students like me (as a sophomore in CS) to achieve our goal with better guidelines.
You are an expert and I understand that. But, it is not appropriate to post comments like that for someone who is a beginner. I asked many of my professors (post doctorate degrees) one question for 20 times and they never let me down or ignore me.
I understand that you might be a very organized person and trying to show others the right way of doing stuff. But, as a member of  EE I would like to be helped and not to get yield at.

gowflow,

Thanks a lot for all your help. You really helped me out a lot on my requests.
I wish all the members were as mature as you and understood people have different styles of learning.

Very Respectfully,
Paul

Author

Commented:
I've requested that this question be deleted for the following reason:

I do not need the solution anymore. I will get all my thoughts together and post a better question with more clarifications later on. &nbsp;This is apparently inappropriate to ask per aikimark said.<br /><br />Thanks,<br />Paul
CERTIFIED EXPERT
Top Expert 2014

Commented:
@Paul

Are these questions part of your coursework?
gowflowPartner
CERTIFIED EXPERT

Commented:
So lets cut it right to the SOLUTION
If your intrested, Paul I can help you with the solution.

For this you need to answer my thread ID: 37095991 and will take it from there. You may keep this question going if you wish and I am sure aikimark's objective is none other than you acheiving what you want as our objective as Experts here is to provide solution for askers even if they sometime follow 'weired routes' if I can call it so. I feel you are a bit confused on how to ask your questions but can understand from your last comment that your aim is to learn so if it is the case, then, I AM ALL YOURS !!!

gowflow
CERTIFIED EXPERT
Top Expert 2014

Commented:
@Paul

Gowflow is willing to work with you on this problem.  Please respond.

Author

Commented:
Thanks gowflow for clarification. I will have my excel sheet all done and ready and then I will post that final excel file so you guys would be able to help me out.
Basically, now I have already created bunch of sheets in my excel file and I want to combine them all together into a single Sheet call Main Sheet. But the problem is that the number of rows in each sheet will change every time.
So, I need to figure out a way to write a macro that would look at the sheet1 (its first row which is the title) and then copies that into main sheet and then looks for sheet2 (its first row which is the title) and copies that to the main sheet after the sheet1 rows that were previously copied.
The reason I point at the titles of each sheet is because the title of the sheets are the only factor that makes them different from each other.

Thank you,
Paul
CERTIFIED EXPERT
Top Expert 2014

Commented:
@Paul

You should post two workbooks containing a 'before' and an 'after' version of the worksheets.

I will stop the scheduled closure of this question.
gowflowPartner
CERTIFIED EXPERT

Commented:
aikimark
do you want me to help Paul in getting a solution for him or you prefer tohandle it as you had answered this question first way before me and I was asked for help in here when the communication was halted ?
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
By the way Paul you do not need to break your head in finding a solution ...
====
So, I need to figure out a way to write a macro that would look at the sheet1
====
All you need to do is simply put your problem and tell us clearly what you want to acheive (pls be concise and NOT REPETITIVE) and let us (Experts) give you the solution.

gowflow
CERTIFIED EXPERT
Top Expert 2014

Commented:
@gowflow

You go ahead.  I suggest you look back at the pauledwardian questions.  You might find some code that will be helpful.  I'm headed out of town for a long weekend and I won't be able to give this problem very much attention.
gowflowPartner
CERTIFIED EXPERT

Commented:
ok aikimark no problem. Knowing your gentlemen style I wouldn't have explected a diffrent answer !
hv a nice weekend.
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
paul still waiting for your input
gowflow

Author

Commented:
SouthMod;

Of course not. This is part my work project. I just mentioned that I'm a computer science freshman at college. I NEVER mentioned this is part of my school project.
We as student work as well otherwise I cannot afford the tuition.

Author

Commented:
Sorry for the delay gowflow.
I attached the sample worksheet. I basically need to copy all the content in order from sheet1, sheet2, sheet3 to the sheet called "Main Sheet".
But the amount of data will change every time in each sheet and the number of rows will change as well.
Group A, B and C rows needs to be hard coded so it separates each sheet's contents in Main Sheet.

Thank you,
Paul Sample.xlsx
gowflowPartner
CERTIFIED EXPERT

Commented:
Your welcome Paul, I'll be looking at this in the course of the day and will get back to you.
gowflow
Partner
CERTIFIED EXPERT
Commented:
Is this what your looking for ?
Make sure your macro settings are enabled and run the file and choose Import or Clear button in Main sheet and see if this is what your looking for. If you run Import twice it will add them at the bottom of existing ones. you can clear any time to test again adding more sheets manually.

Pls let me know your comments
gowflow
Sample.xlsm

Author

Commented:
This looks great gowflow.
Just one more comment. Can you please tell me which part of the code I need to change if the number of sheet changes from 3 to 5or 6?

Thank you,
Paul
gowflowPartner
CERTIFIED EXPERT

Commented:
NONE !!! it will do it automatically. This is why I said try adding sheets and test it
gowflow

Author

Commented:
Perfect, you are the man.

Author

Commented:
Many thanks to gowflow for his assistance on this.

Paul
gowflowPartner
CERTIFIED EXPERT

Commented:
Your welcome glad we got this one to shore. Pls feel free to post a link here for any question you may need help with.
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
correct aikimark. Some users not knowing we are alerted and not knowing or - forgeting to click on related question - miss it ! I'v experienced this couple of times .
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
aikimark question for you
how does the marks comeup for a question ? if all questions are answered yes and grade A does this give an automatic 10 ??? I wonder my feeling is that it is a mix of time to respond + number of threads + interactions + of course the answereing of questions when closing and the grade.

Appreciate to be enlightened !
gowflow
CERTIFIED EXPERT
Top Expert 2014

Commented:
@gowflow

I'm not sure I understand your question.  Points are calculated by multiplying the grade (A=4,B=3,C=2,D=1) by the points assigned to the question.  If the question is a neglected question (NQ) and you are a designated expert (DE) in the question's zone, then 200 points are added to the question points before multiplication.

For this 500 point question, your A grade should have resulted in 2000 expert points.  If it had been a neglected question, you would have received 2800 expert points.
gowflowPartner
CERTIFIED EXPERT

Commented:
no I  mean the points for the question on the top right corner of the question ooops Its the Rating not the points !! sorry for confusion.
gowflow
CERTIFIED EXPERT
Top Expert 2014

Commented:
I don't know the formula used to calculate that rating.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.