VBA Copy columns from one sheet to another

[Melimob]

Hi,

I have a probably very frightening piece of code below. It does the trick but is clunky I know. I just don't have the know how to streamline.

Its a very simple request I'm sure...

Copy columns from one sheet based on table headers in to another based on table headers (they are the same header names in both sheets).

Any advice welcome!

thank you

Sub GetClientData()
'
' GetClientData Macro
'


    Sheets("Clients ").Select
    Range("C3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("C3").Select
    Sheets("ImportCCB").Select
    Range("Table10[Control Centre Company Build]").Select
    Selection.Copy
    Sheets("Clients ").Select
    Range("C3").Select
    ActiveSheet.Paste
    
    Sheets("CC Reconfiguration Data").Select
    Range("b3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Range("b3").Select
    Sheets("ImportCCB").Select
    Range("Table10[Control Centre Company Build]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("b3").Select
    ActiveSheet.Paste
    
    Sheets("ImportCCB").Select
    Range("Table10[CompanyID]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("E3").Select
    ActiveSheet.Paste
    
    Sheets("ImportCCB").Select
    Range("Table10[GDS]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("f3").Select
    ActiveSheet.Paste
    
    Sheets("ImportCCB").Select
    Range("Table10[Current Profile PCC]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("h3").Select
    ActiveSheet.Paste
    
    Sheets("ImportCCB").Select
    Range("Table10[Current Offline PCC]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("i3").Select
    ActiveSheet.Paste
    
    Sheets("ImportCCB").Select
    Range("Table10[Current Online PCC]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("J3").Select
    ActiveSheet.Paste
    
    Sheets("ImportCCB").Select
    Range("Table10[Account Number]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("k3").Select
    ActiveSheet.Paste
    
    Sheets("ImportCCB").Select
    Range("Table10[Account Name]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("L3").Select
    ActiveSheet.Paste
    
    Sheets("ImportCCB").Select
    Range("Table10[Current Bar Title/ Company Profile Name]").Select
    Selection.Copy
    Sheets("CC Reconfiguration Data").Select
    Range("M3").Select
    ActiveSheet.Paste
    
End Sub

 

[Melimob]

You're welcome,
regards, JLG

sorry JLGWhiz

I'm trying to incorporate this two pieces of code to clear contents but leave formulas and can't get it work?
Do you know how I can please?

With Sheets("CC Reconfiguration Data")
       .Range("B3").ListObject.DataBodyRange.ClearContents
    End With


SpecialCells _
          '(xlCellTypeConstants, 23)

 

[JLGWhiz]

sorry JLGWhiz

I'm trying to incorporate this two pieces of code to clear contents but leave formulas and can't get it work?
Do you know how I can please?

With Sheets("CC Reconfiguration Data")
       .Range("B3").ListObject.DataBodyRange.ClearContents
    End With
SpecialCells _
          '(xlCellTypeConstants, 23)

i don't work with tables much, but I belive the syntax is something like this

With Sheets("CC Reconfiguration Data")
       .ListObjects(1).DataBodyRange.ClearContents
End With

I used index 1, but believe you can use the actual table name as it appears in the Workbook Names enclosed in quotation marks instead of the index. I would have to make a table and play around with it myself to be sure about that.

 

[Melimob]

i don't work with tables much, but I belive the syntax is something like this

With Sheets("CC Reconfiguration Data")
       .ListObjects(1).DataBodyRange.ClearContents
End With

I used index 1, but believe you can use the actual table name as it appears in the Workbook Names enclosed in quotation marks instead of the index. I would have to make a table and play around with it myself to be sure about that.

Thanks again. I had this piece ok but needed to include the SpecialCells (xlCellTypeConstants, 23) somewhere so that it wouldn't touch the formulas.

thanks tho!

 

[JLGWhiz]

It is pretty hard to use ClearContents on a range without affecting the formulas that are in that range. If you find a way to do that, let me know. Usually, to clear a range of visible data that is populated by formulas, you have to elimanate the data from the source range(s).