Copy and Paste from one worksheet to another.

[Tonkytonk]

Hi all,

The code I'm about to paste isn't the prettiest but does the job. I created this for another spreadsheet of mine which copied data and transposed it into another table which worked a treat.

The problem I'm currently having is that it will not add the data into the table that I have, or if it does it deletes the data that was just added previously. Any help to a) streamline it or b) get it to copy the data would be awesome.

'
' CopyData Macro
'

    Dim strRng As String
    Dim WKS As String
        
    WKS = Range("C1").Value
    
'Insert a new row
    Application.ScreenUpdating = False
    Sheets(WKS).Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    
' copy and paste data
    Sheets("Input").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Selection.Copy
    Sheets(WKS).Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("Input").Select
    Application.CutCopyMode = False

    
' delete empty row
    Application.ScreenUpdating = False
    Sheets(WKS).Select
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
        
' clear data in b6 to b9
    Sheets("Input").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A4").Value = "Add Data Here"
    Range("C1").Value = "Buddy"

 

[Tonkytonk]

Ok I know why it is overwriting the cells now. because I'm copy over the data.

Now in the worksheet that it is coping to it is a table that has been created using Ctrl T.

When I try to insert the cut cells it says that I can't move the data in the table. Just frustrating that it works for my other table but won't work for this one.