VBA Copy Question

[ionelz]

Hello,
I have this line code : Sheets("Sheet1").Range("C2:C100000").Copy
So range C2:C100000

How do I say this :
Look in A1:Z1 for name "ABC" , say "ABC" is found in cell F1, and Copy that Column
Code would be :
Sheets("Sheet1").Range("F2:F100000").Copy


Thank you

 

[ionelz]

Also, to make sure I added 100000
How do I COUNT how many are in that column, in this case from F2 to bottom, say value of count is N1
Then code would be Sheets("Sheet1").Range("F2:F(N1)").Copy

 

[Joe4]

Try something like this:

Sub MyCopy()

    Dim c As Long
    Dim lr As Long
    Dim cell As Range
    Dim rng As Range
    
    Sheets("Sheet1").Activate
    
'   Find location of "ABC" in row one
    For Each cell In Range("A1:Z1")
        If cell.Value = "ABC" Then
'           Capture column index
            c = cell.Column
            Exit For
        End If
    Next cell
    
'   Exit if value not found
    If c = 0 Then
        MsgBox "ABC not found", vbOKOnly
        Exit Sub
    End If
    
'   Find last row with data in found column
    lr = Cells(Rows.Count, c).End(xlUp).Row
    
'   Build range to copy
    Set rng = Range(Cells(1, c), Cells(lr, c))
    
'   Copy range
    rng.Copy

End Sub

 

[ionelz]

I get message that ABC not found...
I have replaced "ABC" with what I need "BOM Line"
So name I am searching is 2 words

 

[Joe4]

I suspect either a Case issue, or you have extra characters at the end of your entry.
Try this:

Sub MyCopy()

    Dim c As Long
    Dim lr As Long
    Dim cell As Range
    Dim rng As Range
    
    Sheets("Sheet1").Activate
    
'   Find location of "ABC" in row one
    For Each cell In Range("A1:Z1")
        If Left(LCase(cell.Value), 8) = "bom line" Then
'           Capture column index
            c = cell.Column
            Exit For
        End If
    Next cell
    
'   Exit if value not found
    If c = 0 Then
        MsgBox "BOM Line not found", vbOKOnly
        Exit Sub
    End If
    
'   Find last row with data in found column
    lr = Cells(Rows.Count, c).End(xlUp).Row
    
'   Build range to copy
    Set rng = Range(Cells(1, c), Cells(lr, c))
    
'   Copy range
    rng.Copy
    
End Sub

 

[ionelz]

for some reason I can not make it work, get same message
I was trying to re type in source file BOM Line name but still no...

 

[Joe4]

Something must be different, because it works just fine for me.
Please post a sample of your data, so we can see what it looks like.

 

[ionelz]

Here is what I have, now I am lost !
I am opening a source file, do that ....Copy (source file has Sheets1 always) then Paste in my file
I am not sure why is not working, why that BOM Line is not found ...

Private Sub CommandButton1_Click()

Dim FileFolder As FileDialog
Dim FileName As String
Dim FileChosen As Integer
Dim FileSource As Workbook
Dim FileDestination As Worksheet

Dim c As Long
Dim lr As Long
Dim cell As Range
Dim rng As Range

Range("B1").ClearContents
Range("B4:B100000").ClearContents

Set FileDestination = ActiveSheet
'Set FileDestination = Sheets("Sheet1")
Set FileFolder = Application.FileDialog(msoFileDialogFilePicker)
Application.DisplayAlerts = False
FileFolder.Title = "Please Select a folder and file."
FileChosen = FileFolder.Show
FileName = FileFolder.SelectedItems(1)
Set FileSource = Workbooks.Open(FileName)

Sheets("Sheet1").Activate

' Find location of "BOM Line" in row one
For Each cell In Range("A1:Z1")
If Left(LCase(cell.Value), 8) = "BOM Line" Then
' Capture column index
c = cell.Column
Exit For
End If
Next cell

' Exit if value not found
If c = 0 Then
MsgBox "BOM Line not found", vbOKOnly
Exit Sub
End If

' Find last row with data in found column
lr = Cells(Rows.Count, c).End(xlUp).Row

' Build range to copy
Set rng = Range(Cells(1, c), Cells(lr, c))

' Copy range
rng.Copy

FileDestination.Range("B4").PasteSpecial xlPasteValues
FileDestination.Range("B1").Value = FileName
FileSource.Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[ionelz]

This is what I have right now and work perfect
But I want to replace that C2:C100000 with find BOM Line in A1:Z1


Private Sub CommandButton1_Click()

Dim FileFolder As FileDialog
Dim FileName As String
Dim FileChosen As Integer
Dim FileSource As Workbook
Dim FileDestination As Worksheet

Range("B1").ClearContents
Range("B4:B100000").ClearContents

Set FileDestination = ActiveSheet
'Set FileDestination = Sheets("Sheet1")
Set FileFolder = Application.FileDialog(msoFileDialogFilePicker)
Application.DisplayAlerts = False
FileFolder.Title = "Please Select a folder and file."
FileChosen = FileFolder.Show
FileName = FileFolder.SelectedItems(1)
Set FileSource = Workbooks.Open(FileName)

Sheets("Sheet1").Range("C2:C100000").Copy

FileDestination.Range("B4").PasteSpecial xlPasteValues
FileDestination.Range("B1").Value = FileName
FileSource.Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[ionelz]

In above, BOM Line I KNOW is at C1 so I copy C2:C100000
In other files, BOM Line is at B1 but code is at ....C2