Vlookup, hlookup, match ???

[samdev]

I have a workbook with the two sheets.

Sheet 1 contains in Column A various categories and the next 31 column
headings represent a date (day for each day of the month) the dollar
amount associated with each category by day.

Sheet 2 contains in column A some of the various categories from Sheet
1 and in Sheet2!B1, I would like to insert the date and it would
automatically populate the corresponding values from Sheet 1 for each
category listed on Sheet 2 for the filled in date.

Not sure the best way to do this.....

Thx!!

 

[Guest]

Belwo si code that may work. I don't know if the date in Sheet one in row 1
is a date or just the numbers 1 to 31. I assumed the row contains actual
dates. if itis just numbers the make the following change
from:
If .Cells(1, columncount).Value = _
Target Then
to:
If .Cells(1, columncount).Value = _
day(Target) Then

The worksheet change gets placed in the VBA sheet page, not a module.
To add, go to the second sheet of workbokk and right click tab at botom of
sheet (normally sheet2). the select view code. Copy code and insert in VBA
page.



Sub worksheet_change(ByVal Target As Range)

If Target.Column = 1 Then
With Sheets("sheet1")
LastColumn = .Cells(1, Columns.Count). _
End(xlToLeft).Column
For columncount = 2 To LastColumn
If .Cells(1, columncount).Value = _
Target Then

.Range(.Cells(2, columncount), _
.Cells(11, columncount)).Copy

Range("B" & Target.Row).Select
Selection.PasteSpecial _
Transpose:=True
End If
Next columncount
End With

End If

End Sub

 

[samdev]

Belwo si code that may work. I don't know if the date in Sheet one in row 1
is a date or just the numbers 1 to 31. I assumed the row contains actual
dates. if itis just numbers the make the following change
from:
If .Cells(1, columncount).Value = _
Target Then
to:
If .Cells(1, columncount).Value = _
day(Target) Then

The worksheet change gets placed in the VBA sheet page, not a module.
To add, go to the second sheet of workbokk and right click tab at botom of
sheet (normally sheet2). the select view code. Copy code and insert in VBA
page.

Sub worksheet_change(ByVal Target As Range)

If Target.Column = 1 Then
With Sheets("sheet1")
LastColumn = .Cells(1, Columns.Count). _
End(xlToLeft).Column
For columncount = 2 To LastColumn
If .Cells(1, columncount).Value = _
Target Then

.Range(.Cells(2, columncount), _
.Cells(11, columncount)).Copy

Range("B" & Target.Row).Select
Selection.PasteSpecial _
Transpose:=True
End If
Next columncount
End With

End If

End Sub









- Show quoted text -

Did this and nothing happened - I'm I missing something???

Thx,

 

[Guest]

I don't know how well you know VBA on the best way to proceeed. I tested the
code by put the dates from 1/1/07 to 1/31/07 on worksheet SHEET1 in cells
B1:AG1. I also put the numbers 1 to 10 in cells K2 to K11 which was under
the data 1/10/07

I then went to the worksheet page where on put the code (I used sheet2). and
in Column A I typed the date 1/10/07. The numbers 1 to 10 appeared on sheet2
next to the the date 1/10/07.

Get this working. then modify the code to copy the cells you needed copied.

 

[samdev]

I don't know how well you know VBA on the best way to proceeed. I tested the
code by put the dates from 1/1/07 to 1/31/07 on worksheet SHEET1 in cells
B1:AG1. I also put the numbers 1 to 10 in cells K2 to K11 which was under
the data 1/10/07

I then went to the worksheet page where on put the code (I used sheet2). and
in Column A I typed the date 1/10/07. The numbers 1 to 10 appeared on sheet2
next to the the date 1/10/07.

Get this working. then modify the code to copy the cells you needed copied.







- Show quoted text -

Got this to work - but I only want certain numbers copied to
sheet2....For example; in Column A sheet 1 - I have categories and not
all categories need to be copied to Sheet2 - for example, in your
scenario - I may only need K2, K6, K10 on sheet2. and these values on
sheet2 need to be below the date on Sheet2 not to the right.

I'm new to VB so I do appreciate your help and your patience.

Thx,

 

[Guest]

I changed the first if statement
from
If Target.Column = 1 Then
to
If Target.Row = 1 Then

Now if you enter date on first row (used to be first column) the data will
be copied. Gave some examples of copying individual cells from one sheet to
the 2nd sheet.

Sub worksheet_change(ByVal Target As Range)

If Target.Row = 1 Then
With Sheets("sheet1")
LastColumn = .Cells(1, Columns.Count). _
End(xlToLeft).Column
For columncount = 2 To LastColumn
If .Cells(1, columncount).Value = _
Target Then

Cells(4, Target.Column) = _
.Cells(5, columncount)

Cells(7, Target.Column) = _
.Cells(6, columncount)

Cells(9, Target.Column) = _
.Cells(7, columncount)

End If
Next columncount
End With

End If

End Sub

 

[samdev]

I changed the first if statement
from
If Target.Column = 1 Then
to
If Target.Row = 1 Then

Now if you enter date on first row (used to be first column) the data will
be copied. Gave some examples of copying individual cells from one sheet to
the 2nd sheet.

Sub worksheet_change(ByVal Target As Range)

If Target.Row = 1 Then
With Sheets("sheet1")
LastColumn = .Cells(1, Columns.Count). _
End(xlToLeft).Column
For columncount = 2 To LastColumn
If .Cells(1, columncount).Value = _
Target Then

Cells(4, Target.Column) = _
.Cells(5, columncount)

Cells(7, Target.Column) = _
.Cells(6, columncount)

Cells(9, Target.Column) = _
.Cells(7, columncount)

End If
Next columncount
End With

End If

End Sub








- Show quoted text -

Thank you - works great - much appreciated!!!