Copying Cell range values to another workbook

[tjc154]

I want to write a macro that copies values from cell range A2:AG50 (Sheet1) to another workbook titled (Remediation) if data in column “F” ( Sheet 1) titled Application Coordinator matches the value Bob Jones.

The origination workbook where the data resides is titled Dashboard.

The destination workbook where data will be copied to is titled Remediation Status and the worksheet name is called “Status”

Thoughts?

Thank you

Tom

 

[Datsmart]

This code will copy all Rows with "Bob Jones" in column F to the Target workbook named "Remediation":

Sub CopyBobJones()
'Assign Source Variables
    Var1 = "Bob Jones"
    wbs = "Dashboard.xlsm"
    wsS = "Sheet1"
'Assign Target Variables
    wbt = "Remediation.xlsm"
    wst = "Status"
'Clear Target of old data
    Workbooks(wbt).Sheets(wst).Cells.Clear
'=====================================
'Use AutoFilter to copy to Target workbook
Set Source = Workbooks(wbs).Sheets(wsS).Range("F1").CurrentRegion
    With Source
        .AutoFilter Field:=6, Criteria1:=Var1
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Workbooks(wbt).Sheets(wst).Range("A1")
    End With
'Turn OFF Autofilter
    Workbooks(wbs).Sheets(wsS).AutoFilterMode = False
'=====================================
End Sub

Code assumes both workbooks are open.
Old data is cleared from the Target workbook each time the code is run.

 

[tjc154]

Thank you this worked great.

Before running the macro I copied over the column headings from the source spread sheet to the destination sheet on row 1. When I run the macro it copies the data but removes the header information from the destination worksheet.

Is there something I need to differently so it does not remove it? Also, is there anything I can add to leave the formatting (row height and column width), and (font type and size) the same when it copies to the destination worksheet.

Thank you

Tom

 

[Datsmart]

Made the following changes:
Changed "Clear" to "ClearContents" to only clear old Data and not the Formatting of Target area.
Removed ".Offset(1, 0)" to Include copying of Header Row to Target.
Changed "Copy Destination" to "Copy PasteSpecial xlValues" to NOT copy format from Source worksheet.

Sub CopyBobJones2()
'Assign Source Variables
    Var1 = "Bob Jones"
    wbs = "Remediation.xlsm"
    wsS = "Sheet1"
'Assign Target Variables
    wbt = "Remediation Status.xlsm"
    wst = "Status"
'Clear Target of old data
    Workbooks(wbt).Sheets(wst).Cells.ClearContents
'=====================================
'Use AutoFilter to copy to Target workbook
Set Source = Workbooks(wbs).Sheets(wsS).Range("F1").CurrentRegion
    With Source
        .AutoFilter Field:=6, Criteria1:=Var1
        .SpecialCells(xlCellTypeVisible).Copy
        Workbooks(wbt).Sheets(wst).Range("A1").PasteSpecial xlValues
    End With
'Turn OFF Autofilter
    Workbooks(wbs).Sheets(wsS).AutoFilterMode = False
'Turn off "Copy" marqee
    Application.CutCopyMode = False
'=====================================
End Sub

Now the Target area will retain its' Formatting and the Header row will be included in the copy.