MS Excel - returning data from 1 workbook to another, with condition

[mack1attack]

I have a workbook that I want to extract only certain data from and brought over to a second workbook. The workbook I’m extracting from has 14 columns (these columns never change) but the number of rows can change weekly.

This is the part I don’t know how to do, I may be biting off more than I can chew here....I would like to extract only the data associated with the one particular name (HCC) under the TITLE column (this column can have 10 or more different title names but I only want info associated with HCC). I in turn want to return data from 7 of the columns (title column is one of the 7) that is associated with this one title name and all rows.

In other words, I have workbook one with columns 1,2,3,4,5,6,7,8,9,10,11,12,13,14. I want the workbook 1 data from columns 1,2,6,8,9,11,13 to show in workbook 2, BUT I only want the data associated with name HCC from Column 8.

I'm not sure if this is done with programming, macros or can be done with formulas. Hope this made sense, any help would be great.

 

[SpillerBD]

You will probably need to go VBA-macro because you are bringing limited columns over.
It could be started with the Advanced Filter and then a copy/paste procedure.

HCC? I work for a company that uses that abbreviation to identify themselves...

 

[mumps]

Make sure that both workbooks are open. Place this macro in workbook 1. Change the name of Workbook2 in the code to suit your needs. Change the sheet names to suit your needs.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet1").Range("H1:H" & LastRow).AutoFilter Field:=1, Criteria1:="HCC"
    Sheets("Sheet1").Range("A2:B" & LastRow).SpecialCells(xlCellTypeVisible).Copy Workbooks("Workbook2.xlsx").Sheets("Sheet1").Cells(1, 1)
    Sheets("Sheet1").Range("F2:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy Workbooks("Workbook2.xlsx").Sheets("Sheet1").Cells(1, 3)
    Sheets("Sheet1").Range("H2:I" & LastRow).SpecialCells(xlCellTypeVisible).Copy Workbooks("Workbook2.xlsx").Sheets("Sheet1").Cells(1, 4)
    Sheets("Sheet1").Range("K2:K" & LastRow).SpecialCells(xlCellTypeVisible).Copy Workbooks("Workbook2.xlsx").Sheets("Sheet1").Cells(1, 6)
    Sheets("Sheet1").Range("M2:M" & LastRow).SpecialCells(xlCellTypeVisible).Copy Workbooks("Workbook2.xlsx").Sheets("Sheet1").Cells(1, 7)
    If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[mack1attack]

Thanks...I'm sort of a novice at this, been a LONG TIME since I did Macros....but I'll get this working...

 

[mumps]

Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

 

[mack1attack]

Thanks...I'm sort of a novice at this, been a LONG TIME since I did Macros....but I'll get this working...

 

[mack1attack]

awesome...I'll try this....thank you!

 

[mumps]

You are very welcome.