Find Text Value and Select Range

[Dethpod1]

Hi All,
I'm working on an Excel file that will combine several worksheets into one import sheet. The problem I am having is that I understand how to do this to select the entire row to copy but not just a set range. The below formula works but once I try to add in a second file to the "Import" sheet it states I cannot paste as the area is not the same size.

ThisWorkbook.Worksheets("Master").Rows(1).Find(What:="Qty", LookAt:=xlWhole).EntireColumn.Copy
ThisWorkbook.Worksheets("Import").Range("B1").PasteSpecial xlPasteValuesAndNumberFormats

How can I get the code to find "Qty" and select just the rows beneath it?

Please help!!

Sam G

 

[NdNoviceHlp]

Hi Sam G and Welcome to the Board! Here's some code to do what you want without the copy & paste. Hopefully the comments are informative. HTH. Dave

Sub test()
With Sheets("Master")
'get last used column of row 1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'loop through columns
For Cnt = 1 To LastCol
'find "Qty" in row 1
If .Cells(1, Cnt) = "Qty" Then
'get last row of found column
LastRow = .Cells(.Rows.Count, Cnt).End(xlUp).Row
'set range with found column & lastrow
Set Rng = .Range(.Cells(1, Cnt), .Cells(LastRow, Cnt))
Exit For
End If
Next Cnt
End With
'transfer range to anchor location and resize
Sheets("Import").Cells(1, "B").Resize(Rng.Rows.Count, _
            Rng.Columns.Count).Cells.Value = Rng.Cells.Value
End Sub

 

[DanteAmor]

I'm working on an Excel file that will combine several worksheets into one import sheet.

If you want to combine all the sheets in the workbook that contain the word "Qty" in row 1, inside the sheet "Import", then try the following:

Sub CopyRange()
  Dim sh As Worksheet, shI As Worksheet
  Dim f As Range
  Dim lr As Long
  
  Set shI = Sheets("Import")
  
  For Each sh In ThisWorkbook.Sheets
    If sh.Name <> shI.Name Then
      Set f = sh.Range("1:1").Find("Qty", , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        lr = sh.Cells(Rows.Count, f.Column).End(3).Row - 1
        shI.Range("B" & Rows.Count).End(3)(2).Resize(lr).Value = sh.Cells(2, f.Column).Resize(lr).Value
      End If
    End If
  Next
End Sub

NOTE:

• There are some things you don't explain. What happens if you run the macro again. Should you write below the last cell with data in column B of the "Import" sheet or should you delete column B and start at row 2?
• Do you need the macro only for the "Master" sheet and then manually in the macro will you put another sheet to import?
• You should explain the entire macro you need, since otherwise you will have to adapt some of the solutions proposed here to your final objective.