Column to headings& heading contents to rows

[fiza]

Hi Any one

I have a workbook with two sheets.

One named as data & the other named as Sales.
I need a macro code so that when it is clicked the cell contents of the sheet "data" gets copied into sheet "sales" as mentioned in the attached document sheet.

Thanks in advance.

[Bob Phillips]

You say that you want the data in columns D & E copied to the Sales sheet, but that sheet already has that info, laid out differently, but it is there.

[fiza]

That is just to show you how I want the columns to be copied into the sales sheet. Lets say you delete everything in the sales sheet except the cell that is there to write the accession number.

when the accession number is written. I want the data related to the accession number to be copied into the sales sheet from the data sheet in the manner I have shown in sales sheet of the sample file. I hope I have made my question clear.

[Bob Phillips]

[vba]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2" '<== change to suit
Dim LastRow As Long
Dim NextRow As Long
Dim Category As String
Dim i As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Range("A4").Resize(1000, 2).ClearContents

NextRow = 4
With Worksheets("data")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 5 To LastRow

If .Cells(i, "I").Value2 = Target.Value Then

NextRow = NextRow + 1
If .Cells(i, "D").Value2 <> Category Then

NextRow = NextRow + 1
Category = .Cells(i, "D").Value2
Me.Cells(NextRow, "A").Value2 = Category
End If

Me.Cells(NextRow, "B").Value2 = .Cells(i, "E").Value2
End If
Next i
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[fiza]

Excellent. That was exactly what I was asking for. But I need a little bit of modification if you don't mind. I want the category to appear in column B and the items to appear in column A. Moreover the category name to appear on top row of their respected items with the column heading Product Name. I've attached the document for your reference.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2" '<== change to suit
Dim LastRow As Long
Dim NextRow As Long
Dim Category As String
Dim i As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Range("A4").Resize(1000, 2).ClearContents

NextRow = 4
With Worksheets("data")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 5 To LastRow

If .Cells(i, "I").Value2 = Target.Value Then

NextRow = NextRow + 1
If .Cells(i, "D").Value2 <> Category Then

NextRow = NextRow + 1
Category = .Cells(i, "D").Value2
Me.Cells(NextRow, "B").Value2 = Category
Me.Cells(NextRow, "B").Font.Bold = True
NextRow = NextRow + 1
Me.Cells(NextRow, "A").Value2 = "Product Name"
Me.Cells(NextRow, "A").Font.Bold = True
NextRow = NextRow + 1
End If

Me.Cells(NextRow, "A").Value2 = .Cells(i, "E").Value2
Me.Cells(NextRow, "A").Font.Bold = False
End If
Next i
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[fiza]

Thanks for the modification. That works awesome.



If I may ask one more help.


What I want now is a modification of the code so that when the data from the data sheet gets copied into the sales sheet , column B of each row where the heading "Product Name" exist; the text "Result" to appear. also in column C the text"Units" & in Column D the text "Reference" to appear. I want them to be bold.


Thanks in advance.

[Bob Phillips]

fiza,

Can I ask a question?

Many people come onto this forum with a specific need, they ask their question, get a response, and we likely never see them again. People who tend to hang around do so because they have a project they need to complete which may require many VBA tasks, or they want to learn VBA so that seek every opportunity to ask for assistance. Are you actually trying to develop some skills, do you make any attempt at this yourself, or do you just see us a free resource to help you in your job?

[fiza]

People who tend to hang around do so because they have a project they need to complete which may require many VBA tasks, or they want to learn VBA so that seek every opportunity to ask for assistance. Are you actually trying to develop some skills, do you make any attempt at this yourself, or do you just see us a free resource to help you in your job?

This a very annoying answer from a administrator and the wordings withing the above quotation has no resemblance to me in anyway way. Before asking any question of mine I do try my tasks several times. When I fail to do so, I ask help. When I asked my question first, to avoid confusion I did switch my question into parts.

I guess there are thousand of forums online, which might be mature. Moreover, I thought this was the best forum.

I've read the terms & condition of this forum before signing. But it never pointed out the fact that you're saying "post a thread,get lost and never come back again".

Since this is a forum people who don't know things will post. And if they like the forum they would keep re-posting with more things that they don't know. I feel privileged to help others.

Because God has given knowledge in various amount to different humans; making one another to help each other. If those who have knowledge do hold it without sharing by giving excuses, God has the capability to take it away form them.

So I do believe helping others is getting more knowledge than loosing it.

Anyway I do really appreciate the help you had provided. It was really good & I feel happy for that. On the other hand, I do feel sad to your immature & unprofessional answer to my last question.

[fiza]

Anyways, I got the problem solved for myself. Once again thanks for the help. I do really appreciate that.

[Bob Phillips]

I have provided you a lot of help over the pats few weeks, as have others, but I cannot recall seeing you attempt anything, just ask for others to do the work for you. As such, I feel my question was perfectly valid, it was polite and I wanted to know because it could influence how I respond to any further questions by yourself.

I like this forum because of all of the good people who do administer here, but mainly because we have loads of members who are genuinely intersected in Excel and VBA, and are trying to increase their knowledge (I would cite an example from Pappadopoulos yesterday where he posted working code that he had written, but he felt that it could be improved. I wrote a lengthy and considered response) It is a pleasure to help such people because I know that they will take comments on boards, produce better code next time, and most start contributing and helping others (look at GTO, when he started it was mainly seeking help, now he is primarily helping others, when he isn't flattering us all )

I don't think my record in helping others can be questioned. I have over 17,000 posts here over 5 years, that is close to 10 for every day I have been a member, very few of them were questions on my part, the vast majority are replies trying to help resolve the problems of others. I have well in excess of 50,000 posts elsewhere, again mainly helping others. So I cannot be accused of holding onto my knowledge as you suggest, but that doesn't mean I don't have the right to question as I did.

I am sorry that you have reacted as you have, you made many statements that I did not make ore even allude to and attributed them to. To me that is immature and unprofessional, not the question that I asked. I am not an administrator here, I am just a member like yourself, so what I say is just my view, it is not forum rules. I am not telling you to behave one way or another, I just asked you for my own information. If you continue to post, others may be quite happy that you just wish to use us to do parts of your work for you, I am not happy if that is the case.

It is clear that I have upset you and I have stepped over a line, I will ignore your posts from now on and leave it to others to make their own decisions.

[fiza]

It is clear that I have upset you and I have stepped over a line, I will ignore your posts from now on and leave it to others to make their own decisions.

You cant judge a person the way you like. & you don't have the right to blame for things that I don't do. You keep telling that I'm taking advantage to do projects of myself with free help of others. Its a shame on you to judge people like this.

I was either polite on my answer. I know you are not an administer. I did kept saying several times that I respect your help and appreciate that. The only thing that I didn't like was you blaming me that I'm taking the advantage of this forum to work on my projects and earn money for them.

You also suggest that I'm not helping others in this forum but instead getting others help. For your kind information, I must say that I have come across many threads in this forum where I had the answer but someone had answered the question before me.

If you like to ignore my posts. It's OK. I don't mind. because your life is your own property and its non of my business. But I must say that I appriciate all the help you had provided.

Moreover, I joined this forum to get help and to help others but not to keep you challenging on me.

[mdmackillop]

Hi Fiza,
I've looked thogh the threads you have started, but can't see any significant original code attempts by yourself. XLD is only asking you to comply with our basic premise as stated in our FAQ section. If you post your code attempts, then we will assist.

How to Get Free Help
Free help is for people who have tried to develop their own macro, but are having difficulty; or they're using someone else's code and cannot get it to work properly

We would also expect your worked out solutions to be posted. Our forum is to assist any who read the questions, not only the original posters.

Regards
MD

[fiza]

I'm tired of being what you want me to be, feeling so faithless and lost under the surface . Every step that I take is another mistake to you.

I would be happy if anyone could let me know how to get unsubscribed from this forum as I believe I'm not fit for this forum.