Link to home
Start Free TrialLog in
Avatar of kp
kp

asked on

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

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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

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

Avatar of kp
kp

ASKER

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).
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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.