Consulting

Results 1 to 14 of 14

Thread: Column to headings& heading contents to rows

  1. #1

    Column to headings& heading contents to rows

    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.
    There's no place like HOME
    Regards,
    Fiza

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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.
    There's no place like HOME
    Regards,
    Fiza

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    [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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    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.
    There's no place like HOME
    Regards,
    Fiza

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    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.
    There's no place like HOME
    Regards,
    Fiza

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    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.
    There's no place like HOME
    Regards,
    Fiza

  10. #10
    Anyways, I got the problem solved for myself. Once again thanks for the help. I do really appreciate that.
    There's no place like HOME
    Regards,
    Fiza

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    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.
    There's no place like HOME
    Regards,
    Fiza

  13. #13
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  14. #14
    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.
    There's no place like HOME
    Regards,
    Fiza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •