Column to headings heading contents to rows

[adam]

Hi anyone,

Attached please find the workbook that I want to create for myself. The workbook is having two sheets.

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

I hope I have made my question clear.

I would be pleased if anyone could help me with this topic.

Thanks for taking your valuable time to read this.

Thanks in advance.

[StuartR]

You can get something very close to what you have asked for by using a Pivot Table, this doesn't need any code.

In this example, you change the Accession Number by clicking the filter symbol in cell B24

[adam]

StuartR, I respect your suggestion.

I'm aware that this thing could be done in a pivot table format.

but since I'm trying to create a sales report on the format I have provided, I must say that I would be happy if the row, row labels could be hidden or does not exist.


Also the accession number in your sample does not seem to get updated when I enter a new Accession number in request sheet.

[StuartR]

...Also the accession number in your sample does not seem to get updated when I enter a new Accession number in request sheet.

You can get rid of the "Row labels" cell by simply hiding row 26

If you want to hide the "Grand Total" cell this is a bit harder, but you can set the font and background to both be white.

The reason that the accession number doesn't get updated is because I made the mistake of putting the "Serial" column into the filter field, instead of the Accession number, but you do need to right click the pivot table and select refresh when you change the data.

Is this any better?

[adam]

OK StuartR, here is another try. Lets say when I write the accession number A0001 in cell B2 from the request sheet, the text "fruit" appear in row A3 and all the items related to fruits with the accesion number A0001 appear under the rows Fruits A4:A7. could this be possible.

[HansV]

If you hide row 25 and delete rows 2 through 23 in Stuart's version of the workbook, you'll get exactly what you want (except that the row numbers will be slightly different because of the hidden rows).

[adam]

Thanks Hans & StuartR for the help & recommendations. I've solved the problem without a Pivot table.

[adam]

I have included one more sheet named "Reference" to my workbook named now as "sample". I've also incorporated a code to the sheet "sales" which when I write the accession no in the cell B2 fills the sheet by data copied from the sheet request. I have tried the following code so that when the data gets filled in the sheet "sales" under the rows data from the reference sheet gets copied accordingly. But I could not make the code work. I would be happy if you could trace out what I have done wrong

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim wsh As Worksheet
  Dim rngCell As Range
  Dim strID As String
  Dim rngFound As Range
  Dim r As Long
  Dim n As Long
  Dim m As Long
  Set wsh = Worksheets("Reference")
  m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
  If Not Intersect(Range("A8:A" & Rows.Count), Target) Is Nothing Then
    For Each rngCell In Intersect(Range("A8:A" & Rows.Count), Target)
      strID = rngCell.Value
      dtmMax = DateSerial(1900, 1, 1)
      n = 0
      For r = 9 To m
        If wsh.Range("A" & r) = strID Then
          If wsh.Range("A" & r) > dtmMax Then
            n = r
            dtmMax = wsh.Range("A" & r)
          End If
        End If
      Next r
      If n = 0 Then
        rngCell.Offset(0, 1).Resize(1, 3).ClearContents
        rngCell.Offset(0, 7).Resize(1, 4).ClearContents
      Else
        rngCell.Offset(0, 1) = wsh.Range("C" & n)
        rngCell.Offset(0, 2) = wsh.Range("D" & n)
      End If
    Next rngCell
  End If
End Sub

I've also included the workbook where I have included my code & would like to include the above code with that code so that the explanation that I have mentioned in my workbook gets full filled.

Any help would be kindly appreciated.

[HansV]

I don't understand - the code that you post is from a completely different thread (Post 12023), where you wanted to look up information for the most recent date. There are no dates on the reference sheet, so the code is not applicable.

[adam]

I'm sorry to include the date in the code I have uploaded. Actually the date should be removed from the code. Moreover I have detailed in the attached worksheet how I want the code to be. And to create the string I want to reference from the data in the column A, B C, D of the Reference worksheet.

I hope I have made my question clear.

[HansV]

Below the lines

Code: Select all

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

insert these new lines:

Code: Select all

                    Me.Cells(NextRow, "D").Formula = "=VLOOKUP(A" & NextRow & _
                        ",Reference!A5:C23,MATCH(F2,Reference!A4:C4,0),FALSE)"
                    Me.Cells(NextRow, "D").Font.Bold = False

Currently you won't see any difference when you enter Male or Female since the reference values are the same for both. (If you want to test whether the code works, you should provide DIFFERENT values for males and females).

[adam]

I have changed the values and now I could see the difference. Thanks for the help Hans. But the column Where units exist does not seem to copy? What could be the reason for this? If I may ask

[HansV]

Because I didn't include it in the code. Now that you know how to copy the reference range, you should be able to work out how to copy the units yourself. It's actually easier, since you don't have to take male/female into account.

[adam]

Thanks Hans. I've solved the problem.

[HansV]

Good for you!

[adam]

Thanks for the reply Hans. Here is how I replaced the code

Code: Select all

Me.Cells(NextRow, "C").Formula = "=VLOOKUP(A" & NextRow & _
                        ",Reference!A5:D23,4 ,FALSE)"

I only added the above line. I have added a sub category to the table on reference sheet.

Say for example under the row Apple in column A I have included rows as green apple, and in the row below red apple. Could it be possible that when ever apple in entered into the sales sheet the row with the name green apple and red apple also gets copied with their units and reference range.

I would be pleased if there's a solution.

[HansV]

You'd have to enter "Red Apple" or "Green Apple" instead of just "Apple" in the Product Name column on the Data sheet. Otherwise it becomes too complicated.

[adam]

Hans, I respect your suggestion. But the situation is that I have given different reference ranges to the type of apples which I dont want to appear on the datasheet.

Meaning the price of apple if it is either red or green is same. but their reference range is different. for this reason I have included them in the reference sheet but not in the data sheet.

I hope I have made my question clear.

[HansV]

What you are trying to do is to create a query based on several tables. This is typically something done in a database application such as Microsoft Access.

This time, you want to add a subcategory on the Reference sheet. This will make the code more complicated. Next time, you'll want to further subdivide the Data sheet. With each change, you will need highly customized modifications to the VBA code. Access has the mechanism to query multiple tables built in, without needing VBA code.

If you really want to do this in Excel, you should provide a sample workbook with relevant data.

[adam]

Please find the modified workbook with what I'm requesting for. I have included an age column and what I have said as a sub category.

Any help would be kindly appreciated.