Pasting in transpose

[Haree]

Hello all,

I have a code which copies data from source data sheet and enters it into a different workbook . While pasting I am transposing the data . The data in the destination workbook is in a table format. The copied data when transposed is not adding to the table, I am not sure if my assumption is correct but when I remove transpose it works correctly any help on this regard will help me a lot

Attaching the code here. Thanks for your time and help in advance.

Sub repair()
Dim wbs As Workbook, wbs1 As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Dim cpyrng As Range, pasterng As Range
Dim lr As Long

Set wbs = Workbooks("Repairs Enter.xlsx")
Set wbs1 = Workbooks.Open("\\DESKTOP-165T6DF\Gayathri 3\Repair Slip.xlsm")

Set ws = wbs.Sheets("Data")
Set ws1 = wbs1.Sheets("Master Sheet")

Set cpyrng = ws.Range("B1:B9")
lr = ws1.Range("A" & Rows.Count).End(xlUp).Row
Set pasterng = ws1.Range("A" & lr + 1)


    If ws1.Range("A" & lr).Value = ws.Range("B1") Then
    MsgBox ("Data already updated")
    Else
    cpyrng.Copy
    pasterng.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, skipblanks:=False, Transpose:=True
    wbs1.Save
    wbs1.Close
    End If
    
End Sub

 

[DacEasy]

Hi, I tested your code and it worked perfectly. The only way I could make it not add to the table is if I do not have enough columns. Your range is Set cpyrng = ws.Range("B1:B9"), make sure the table has columns a to i. You can have more columns when pasting but not less. Which is why when you don't transpose it works, because you are only using one column. Hope this helps, if not you may need to post a picture of the table.

 

[Haree]

Thanks DacEasy for your reply. My table had sufficient columns, but it was defined which ultimately stopped it from adding rows. Extremely sorry for the trouble.