Macro to Find the last row of a section and paste into that area?

[tonywatsonhelp]

Hi Everyone,
I have a bit of a problem,

I have a sheet called Lists,

In this sheet I have lots of products
Column A is the SKU for each product,
Column B serves a dual propose, not only does it hold the product description but also the Category name
So if you can imagine all the products laid out in rows
Column B holds the Categories, and then below that are all the products for that Categorie.

fortunately the Categories are separated by at least one empty row.

So heres what i need,

A Macro to look at the product category name in sheet Control D4

Find that name in Sheet "Lists" column B and then find the first blank line in column A after it and select it

please help if you can

Thanks

Tony

 

[maabadi]

Try this:

Sub Test()
Dim WS1 As Worksheet, WS2 As Worksheet, M As Long, N As Long
Set WS1 = Sheets("Control")
Set WS2 = Sheets("List")
M = Application.WorksheetFunction.Match(WS1.Range("D4"), WS2.Range("B1", WS2.Range("B" & Rows.Count).End(xlUp)), 0)
If WS2.Range("A" & M).Value = "" Then
N = M
ElseIf WS2.Range("A" & M + 1).Value = "" Then
N = M + 1
Else
N = WS2.Range("A" & M).End(xlDown).Row + 1
End If
WS2.Activate
WS2.Range("A" & N).Select
End Sub

 

[tonywatsonhelp]

This looks great thank you so much for your help
Tony