Transfer data to another workbook

[chunu]

Hi friends,
I have invoice and sales sheets on my workbook,when i save invoice data copied to same workbook sheet "sales".
I want to transfer data to another workbook. Path c:\workbook2.xlsm
below is my code.
Thanks

Sub SavingSalesData()
Dim rng As Range
  Dim i As Long
  Dim a As Long

  Dim rng_dest As Range
  Application.ScreenUpdating = False
  'Check if invoice # is found on sheet "Sales"
  i = 2
  Do Until Sheets("Sales").Range("C" & i).Value = ""
    If Sheets("Sales").Range("C" & i).Value = Sheets("Invoice").Range("E3").Value Then
      'Ask overwrite invoice #?
      If MsgBox("Invoice Number Already Used- Do you want to copy over?", vbYesNo) = vbNo Then
        Exit Sub
      Else
        Exit Do
      End If
    End If
    i = i + 2
  Loop
  i = 1
  Set rng_dest = Sheets("Sales").Range("d:i")
  'Delete rows if invoice # is found
  Do Until Sheets("Sales").Range("C" & i).Value = ""
    If Sheets("Sales").Range("C" & i).Value = Sheets("Invoice").Range("E3").Value Then
      Sheets("Sales").Range("C" & i).EntireRow.Delete
      i = 1
    End If
    i = i + 1
  Loop
  ' Find first empty row in columns C:K on sheet Sales

    Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0
    i = i + 2
  Loop
  'Copy range A8:E27 on sheet Invoice
  With Sheets("Invoice")
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set rng = .Range(.Cells(1, 1), .Cells(lastRow, 7))
End With
  ' Copy rows containing values to sheet Sales
 For a = 8 To rng.Rows.Count

    If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then
      rng_dest.Rows(i).Value = rng.Rows(a).Value
      'Copy Invoice number
      Sheets("Sales").Range("a" & i).Value = Sheets("Invoice").Range("E3").Value
      'Copy Date
      Sheets("Sales").Range("b" & i).Value = Sheets("Invoice").Range("f4").Value
      'Copy Company name
      Sheets("Sales").Range("c" & i).Value = Sheets("Invoice").Range("d6").Value
      'Copy Telephone
      Sheets("Sales").Range("d" & i).Value = Sheets("Invoice").Range("f6").Value
      'discount
      Sheets("Sales").Range("j" & i).Value = Sheets("Invoice").Range("f27").Value
      
      i = i + 1
    End If
  Next a
  Application.ScreenUpdating = True
End Sub

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-pr...248379-transfer-data-to-another-workbook.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[chunu]

Hi friends,
Sorry i did not mentioned that this question was cross-posted at
https://www.excelforum.com/excel-pr...248379-transfer-data-to-another-workbook.html
Sorry for forums rule violation.