CODEWITHSUNDEEP
excelvba
Tilen
Tilen

Reputation: 15

How do i paste data into another workbook?

My code work as I want, but only in working workbook.

I want to Transfer data into another workbook but if I change name and path it doesnt work.

EG: in code where it is 

Worksheets("SHIPMENTS").Paste Destination:=Worksheets("SI").Cells(b + 1, 2)

i used ->

  Worksheets("SHIPMENTS").Paste 
  Destination:=Workbooks("Name").Worksheets("SheetName").Cells(b + 1, 2)"

and it wasn't working.

The code looks like this:

Private sub BTN
a = Worksheets("SHIPMENTS").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
    'based by country
    If Worksheets("SHIPMENTS").Cells(i, 5).Value = "SI" Then

        'if already sent
        If Worksheets("SHIPMENTS").Cells(i, 9).Value = "YES" Then

            'sorting by color
            If Worksheets("SHIPMENTS").Cells(i, 5).Interior.Color = 16777215 Then

                'kopiranje (datuma)
                Worksheets("SHIPMENTS").Cells(i, 3).Copy

                'counting
                b = Worksheets("SI").Cells(Rows.Count, 1).End(xlUp).Row

                'paste 1
                Worksheets("SHIPMENTS").Paste Destination:=Worksheets("SI").Cells(b + 1, 1)


                'copy paste

                'country
                 Worksheets("SHIPMENTS").Cells(i, 5).Copy
                 Worksheets("SHIPMENTS").Paste Destination:=Worksheets("SI").Cells(b + 1, 2)

                'quty
                 Worksheets("SHIPMENTS").Cells(i, 10).Copy
                 Worksheets("SHIPMENTS").Paste Destination:=Worksheets("SI").Cells(b + 1, 3)

                'transfer note
                 Worksheets("SHIPMENTS").Cells(i, 6).Copy
                 Worksheets("SHIPMENTS").Paste Destination:=Worksheets("SI").Cells(b + 1, 4)

                'transfer number
                 Worksheets("SHIPMENTS").Cells(i, 8).Copy
                 Worksheets("SHIPMENTS").Paste Destination:=Worksheets("SI").Cells(b + 1, 5)

                'month
                 Worksheets("SHIPMENTS").Cells(i, 12).Copy
                 Worksheets("SHIPMENTS").Paste Destination:=Worksheets("SI").Cells(b + 1, 7)

                 'coloring
                 Worksheets("SHIPMENTS").Cells(i, 5).Interior.ColorIndex = 43

                 'to the first position
                 Worksheets("SHIPMENTS").Activate
            End If
        End If
    End If
Next
 MsgBox "Copied"
Application.CutCopyMode = True
ThisWorkbook.Worksheets("SHIPMENTS").Cells(1, 1).Select

End Sub

Upvotes: 0

Views: 40

Answers (1)

Tim Williams
Tim Williams

Reputation: 166256

Like this:

Worksheets("SHIPMENTS").Cells(i, 5).Copy Destination:=Worksheets("SI").Cells(b + 1, 2)

Better - declare some worksheet variables and use those to avoid all that repetition.

Upvotes: 1

Related Questions