Copy and paste data from one xlsx sheet to a new excel sheet (in sheet2) using VBScripts

kp
kp used Ask the Experts™
on
I am looking to copy and paste data from one xlsx sheet to a new excel sheet using VBScripts . Could please some one help me using Vb script to paste exact contents from
excel-input.xlsx to a new excel excel-output,xlsx on sheet2   and rename sheet2 to DataSet.

Here is the

Excel-Input.xlsx
Name      age      gender      department      year
don                23      male      computer      2010
ptalakes       24      female      maths              2015
chang      28      male      arts                2016
prag      29      female      science              2018
tom               26      male      arts                      2017
john      27      female      sports              2018
simon      22      male      arts                      2018

Excel-Output.xlsx  (Rename sheet2 as data )
Name      age      gender      department      year
don                23      male      computer      2010
ptalakes       24      female      maths              2015
chang      28      male      arts                2016
prag      29      female      science              2018
tom               26      male      arts                      2017
john      27      female      sports              2018
simon      22      male      arts                      2018
Excel-input.xlsx
Excel-Output.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You may try something like this...
Dim xlApp
Dim wbSource,wbDest
Dim wsSource,wsDest
Dim FolderPath
Dim strSourceFileName
Dim strDestFileName

'The following line assumes that both the files are saved on Desktop. Change the Folder Path as per your requirement.
FolderPath = "C:\Users\<YourUserNameHere>\Desktop\Test\"	'Remember to add a back slash in the folder path in the end

strSourceFileName = "Excel-Input.xlsx"		'Source File Name
strDestFileName = "Excel-Output.xlsx"		'Destination File Name

Set xlApp=CreateObject("Excel.Application")

Set wbSource = xlApp.Workbooks.Open(FolderPath & strSourceFileName)
Set wsSource = wbSource.Worksheets("Data")	'Source Data Sheet

Set wbDest = xlApp.Workbooks.Open(FolderPath & strDestFileName)
Set wsDest = wbDest.Worksheets(2)		'Destination Sheet
wsDest.cells.clear

wsSource.range("A1").currentregion.copy wsDest.range("A1")
wsDest.name="DataSet"

wbSource.Close False
wbDest.Close True

MsgBox "The data has been copied successfully."

Open in new window

kpconsultant

Author

Commented:
Thanks Subodh !

Need one more favor !! Would appreciate your help !


VBScript changes dates format upon converting .txt data  to .xlsx sheet.  Date format "DD/MM/YYYY" in Input.txt changes to "YYYY/MM/DD"  upon saved in excel sheet .


Input.txt  

Row Number|Row Action|Department|DateOfService

123456          |Insert          |Airlines         |12/02/2016
563455          |Insert          |Engineering |24/12/2013
567897          |Insert          |Science         |01/12/2011
356434          |Update       |Airlines         |23/09/2013


Date format of DateOfService  (DD/MM/YYYY) changed to (YYYY/MM/DD) . Could you please help to resolve this issue in VBScript.

i.e DateOfService in output file converted to (YYYY/MM/DD).
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
VBScript changes dates format upon converting .txt data  to .xlsx sheet.  Date format "DD/MM/YYYY" in Input.txt changes to "YYYY/MM/DD"  upon saved in excel sheet .
What VBScript do you use to read the data from a text file into an excel file?

Well, this is a different question altogether. So I suggest you to close this question after accepting the solution and open a new question with your new requirement and upload the sample text file and excel file with the desired output. Also, don't forget to post the VBScript you are using to read the text file.

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