Help with formula and Syntax

[NearlyThere]

Hello, need help please with a Macro copying data from a "Source" to "Target" worksheet using the column heading (In this example "ITEM"). It currently includes hidden cells (filtered in "Source"). Id like to exclude these cells from coping to Target. Struggling to incorporate SpecialCells(xlCellTypeVisible).

Dim ws As Worksheet
Dim aCell As Range, Rng As Range
Dim col As Long, lRow As Long
Dim colName As String

'~~> Change this to the relevant sheet
Set ws = ActiveWorkbook.Sheets("Source")


'ITEM Copy

With ws
Set aCell = .Range("A1:E1").Find(What:="ITEM", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False)

'~~> If Found
If Not aCell Is Nothing Then
col = aCell.Column
colName_ITEM_Name = Split(.Cells(, col).Address, "$")(1)
'MsgBox "colname_ITEM_Name " & colName_ITEM_Name

lRow = .Range(colName_ITEM_Name & .Rows.Count).End(xlUp).Row
MsgBox "Columns Copied = " & lRow
'~~> This is your range
Set Rng = .Range(colName_ITEM_Name & "2:" & colName_ITEM_Name & lRow)

Debug.Print Rng.Address
'~~> If not found
Else
MsgBox "ITEM Count Not Found"
End If
End With

Sheets("Target").Range(colName_ITEM_Name & ":" & colName_ITEM_Name & lr).Copy Sheets("Target").Range("A" & Rows.Count).End(xlUp).Offset(0)

Range("A1").Select

 

[gordsky]

I might be missing something but here

Set Rng = .Range(colName_ITEM_Name & "2:" & colName_ITEM_Name & lRow)

You seem to set a range but then do nothing with it

Here you appear to copy from target to target. Is there more code elsewhere.

Sheets("Target").Range(colName_ITEM_Name & ":" & colName_ITEM_Name & lr).Copy Sheets("Target").Range("A" & Rows.Count).End(xlUp).Offset(0)

Am I correct in thinking what you want to do is find the column header named “item” on the source sheet and then copy the column (less the header) to column a on the target sheet.?

 

[NearlyThere]

Hi, that is correct. The column location changes often. So locator the data below the required header and paste into a new worksheet with another column name. Thanks

 

[gordsky]

whats the column name going to be on the new worksheet

 

[gordsky]

Is this what you need??

option explicit
option compare text
Sub copyme()
Dim ws As Worksheet
Dim aCell As range, Rng As range
Dim col As Long, lRow As Long, lr As Long
Dim colName As String, colName_ITEM_Name As String

'~~> Change this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Source")


'ITEM Copy

Set aCell = ws.range("A1:E1").Find(What:="ITEM", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False)

'~~> If Found
If Not aCell Is Nothing Then

    lRow = ws.Cells(Rows.count, aCell.Column).End(xlUp).row

    ws.range(ws.Cells(2, aCell.Column), ws.Cells(lRow, aCell.Column)).SpecialCells(xlVisible).Copy Sheets("Target").range("A2")

range("A1").Select

Else
MsgBox "ITEM Count Not Found"
End If

End Sub

I changed your code a bit as in the form you posted if "ITEM" wasnt found the sheet would still try to copy a range and throw you an error. Ive moved the copy statement inside the If to prevent this happening.