VBA 07 - Generate Master List from Wsheets, VBA Copy/lookup function for multiple Wsheets

[T-rev]

I am new to VBA and have nearly acheived my desired outcome for this project... only 2 problems left..

1. i would like it to find every other sheet (except the Master List and Market Summary, and return the value of the cells in the range, using row count, Beginning at cell A5 for each sheet.

then Return the value of the first sheet range in A3 of the Master list, and the values of the next sheet one row below the last cell with a value in it,

if it helps, the column range is A:K only.

it's almost working, although i keep getting headers from the sheets in between the data is the only problem and can't find where i can specify exactly what cell to start at/how to reference it properly in VBA. the code i have so far is...

Private Sub CommandButton2_Click()
' CommandButton2_Click Macro
'
'
Dim ws As Worksheet
Dim LR As Long      'used to get the last row of data on each data sheet

With Sheets("Stock Picker")                   'put the name of your master sheet here
    .UsedRange.Offset(2).ClearContents  'remove data, leave titles in row 1
    
     For Each ws In Worksheets
        If ws.Name <> .Name And ws.Name <> "Market OverView" Then        'skip these sheets, use all others
            LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A3:K" & LR).Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1)
        End If

Next ws
End With
    
  
End Sub

2. I have column L as a Category column, and 10 sheets with the names of the Categories in cell A1 of each sheet.

every sheet has a list of codes e.g XYZ,ARV etc (there are no duplicates).

i require a peice of code that looks up the code in column A on the master list, finds it on 1 of the sheets, and returns the value in cell A1 of that sheet

preferably using paste special - values only.

perhapse some code using a lookup function?