copy and paste range based on cell criteria

[Taff]

i need to look up the criteria in workbook "master" worksheet column B, that criteria will be "A1", then copy the adjacent cells in columns C, D, E

I then need to start pasting the info into a work book "slave" in worksheet A1, that already exists, starting in cell A10.

there may be 10 rows of data to copy, there may be only 4. Not worried about having to overwrite each time info pasted.

Would prefer formula rather than VBA.

can this be done?

 

[mumps]

that criteria will be "A1"

Do you mean the text "A1" or the value in cell A1? What is the name of the worksheet in the "master" workbook? What is the full name of the "Slave" workbook including extension (xlsx, xlsm)? Will the "Slave" workbook be open or does it have to be opened by the macro. If it has to be opened, what is the full path to the folder where it is saved?

 

[Taff]

text "A1" and this could be anywhere within column B
Worksheet in Master is called Fixture
Workbook Slave is .xlsx
Both workbooks will already be open

 

[mumps]

Place this macro in the Master workbook. The macro assumes you have headers in row 1 and your data starts in row 2. Change the column letter (in red) to the last used column in your "Fixture" sheet.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim lRow As Long, desWS As Worksheet
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set desWS = Workbooks("Slave.xlsx").Sheets("A1")
    Range("A1:E" & lRow).AutoFilter Field:=2, Criteria1:="A1"
    Intersect(Rows("2:" & lRow), Range("C:E")).Copy desWS.Range("A10")
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Taff]

Hi,

Thanks for this,

but just seems to run on as though its searching for something.

I have had to force stop it and no sign of it picking up a result or attempting to place any data in the destination.

Is it missing something?

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your "Fixture" sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).