Copying data from oneand pasting in anotherrow wise

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:22 PM GMT

Question

QUESTION: I have two excels. Excel1.xls and Excel2.xls. I have some data in excel1.xls. I want to copy data cell wise from Excel1.xls to Excel2.xls. I am able to copy all data from Excel.xls to Excel2.xls. I am also able to give the row count and column count. But I need to copy the data cell-wise using for loop. Kindly help. I am attaching the code that I have written:

Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True

Set objWorkbook1 = objExcel.Workbooks.Open("C:\Documents and Settings\sugandhesh.pal\Desktop\ab.xls") Set objWorkbook2 = objExcel.Workbooks.Open("C:\Documents and Settings\sugandhesh.pal\Desktop\cb.xls")

Set c=objWorkbook1.worksheets("Sheet1") column_count=c.usedrange.columns.count row_count= c.usedrange.rows.count Msgbox "No.of Columns="&column_count Msgbox "No.of Rows="&row_count



objWorkbook1.Worksheets("Sheet1").UsedRange.Copy objWorkbook2.Worksheets("Sheet1").Range("A1").PasteSpecial Paste = xlValues


Set d=objWorkbook2.worksheets("Sheet1") column_count=d.usedrange.columns.count row_count= d.usedrange.rows.count Msgbox "No.of Columns="&column_count Msgbox "No.of Rows="&row_count

objWorkbook1.Save objWorkbook2.Save objWorkbook1.Close objWorkbook2.Close

Set objExcel = Nothing Set row_count= Nothing

ANSWER: Some questions:

1. Why do you want to copy cell-by cell? 2. Are you doing this fromVBA?

---------- FOLLOW-UP ----------

QUESTION: Thank you Jan for replying. Yes I want to copy data cell by cell to another excel. I am using VB Scripting

Answer

If you're just copying values, you can use one single command for the actual copy:

Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True

Set objWorkbook1 = objExcel.Workbooks.Open("C:\Documents and Settings\sugandhesh.pal\Desktop\ab.xls") Set objWorkbook2 = objExcel.Workbooks.Open("C:\Documents and Settings\sugandhesh.pal\Desktop\cb.xls")

Set c=objWorkbook1.worksheets("Sheet1") column_count=c.usedrange.columns.count row_count= c.usedrange.rows.count Msgbox "No.of Columns="&column_count Msgbox "No.of Rows="&row_count


'The part "c.Cells(1,1).Address" 'is used to determine the starting cell of the target range, 'because the usedrange does not necessarily 'start at cell A1

'The lines below are all one statement objWorkbook2.Worksheets("Sheet1").Range( _ c.Cells(1,1).Address).Resize(row_count,column_count).Value= _ c.Value

Advertisement

©2017 eLuminary LLC. All rights reserved.