HLookup VBA Question

[Sjoewie]

Hi,

I am struggling with getting code for the following. In short I would like to use HLOOKUP to find a value in Sheet 2 and then copy and paste all the data below into Sheet 1.

So simple example

Sheet 2 (Upper left cell is A1):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apr
[/TD]
[/TR]
[TR]
[TD]44
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]17
[/TD]
[TD]35
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]19
[/TD]
[TD]31
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD]45
[/TD]
[TD]1
[/TD]
[TD]12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15
[/TD]
[TD]11
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]46
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 (assume upper left is A1 and B1 is the input variable.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Month
[/TD]
[TD]Feb
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The issue I am having is that I struggle how to cope with the different amount of rows that I need to copy.

Any help would be greatly appreciated.

Thanks!

 

[bbott]

Welcome to the board.

This solution doesn't use HLOOKUP, but it should work for you. It pastes the info below B1 in sheet 1.

Sub CpyMnth()

    Dim x As Long
    Application.ScreenUpdating = False
    
    Range("B2:B1000").ClearContents
    x = WorksheetFunction.Match(Sheets(1).Range("B1"), Sheets(2).Range("A1:L1"), 0)
    
    Sheets(2).Select
    Range(Cells(2, x), Cells(Sheets(2).UsedRange.Rows.Count, x)).Copy Sheets(1).Range("B2")
    Sheets(1).Select
    
    Application.ScreenUpdating = True
    
End Sub

 

[Sjoewie]

Great it worked!! If I am correct u used Match instead of HLookup right?

 

[bbott]

Great it worked!! If I am correct u used Match instead of HLookup right?

That is correct.