copy and paste the  data from one file to another file

Sachin Singh
Sachin Singh used Ask the Experts™
on
my 1st file name is OneClick.xlsb  located in C:\Users\user\Desktop
my 2nd file name is BasketOrder.csv located in C:\Users\user\Desktop
my code will be placed in OneClick.xlsb
now what i want copy sheet5 from OneClick.xlsb and paste to BasketOrder.(this is a sheet name in BasketOrder.csv ) of  BasketOrder.csv
(If there is a data in  BasketOrder.csv file then first delete the data then paste the data)
plz see the attached  file
OneClick.xlsb
BasketOrder..csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Why not just save the sheet to csv?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...
Following code assumes that both the .xlsb and .csv files are saved in the same folder.

Sub SaveSheet5AsCSV()
Dim ws As Worksheet
Dim strFilePath As String, strFileName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

strFilePath = ThisWorkbook.Path & "\"
strFileName = "BasketOrder.csv"
Set ws = Sheets("Sheet5")
ws.Copy
ActiveWorkbook.SaveAs strFilePath & strFileName, xlCSV, , , , False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Sheet5 has been saved as csv file successfully.", vbInformation
End Sub

Open in new window

In the attached, click the button called "SaveAs CSV" to run the code.
OneClick.xlsb

Author

Commented:
Thnx Neeraj Sir & Roy Cox Sir for ur great Support
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

Commented:
Sorry

Had to nip out. Here's my suggestion

Option Explicit


Sub SaveSheetToCSV()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False

        ''/// sheet to copy to csv. Copy will copy the sheet to a new workbook automatically because no workbook is specified
        ThisWorkbook.Sheets("Sheet5").Copy
        ''// save as csv file, use NOW to add unique identifier
        ActiveWorkbook.SaveAs ThisWorkbook.Path & .PathSeparator & "BasketOrder_" _
                            & Format(Now, "ddmmyyyy hhmm"), xlCSV, False
        ''/// close csv
        ActiveWorkbook.Close

        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    MsgBox "Done", vbInformation
End Sub

Open in new window

OneClick.xlsb
Roy CoxGroup Finance Manager

Commented:
Bit of a rush!!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Sachin!

Author

Commented:
Thnx Roy Cox Sir for ur great support

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial