I have 2 excel workbooks, and I was curious how I would copy one worksheet of one workbook to another worksheet on the other workbook? This is what I have.. but it takes like 2 minutes to copy everthing over, and it isn't that big. Is there an easier way?
Code:
Dim pathToSheetOne, pathToSheetTwo As String
Dim I, J As Integer
pathToOne = CurDir() & "\one.xls"
pathToTwo = CurDir() & "\two.xls"
Dim oExcel As Excel.Application
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet
oExcel = New Excel.Application
obook = oExcel.Workbooks.Open(pathToOne)
osheet = obook.Worksheets(1)
'Open Up Other Workboook
Dim twoExcel As Excel.Application
Dim twoBook As Excel.Workbook
Dim twoSheet As Excel.Worksheet
twoExcel = New Excel.Application
twoBook = twoExcel.Workbooks.Open(pathToTwo)
twoSheet = twoBook.Worksheets(1)
'Copy over using nested for loops
For I = 1 To 10 Step 1
For J = 1 To 1000 Step 1
osheet.Cells(J, I).Value = twoSheet.Cells(J, I).Value
Next J
Next I
Code:
Dim pathToSheetOne, pathToSheetTwo As String
Dim I, J As Integer
pathToOne = CurDir() & "\one.xls"
pathToTwo = CurDir() & "\two.xls"
Dim oExcel As Excel.Application
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet
oExcel = New Excel.Application
obook = oExcel.Workbooks.Open(pathToOne)
osheet = obook.Worksheets(1)
'Open Up Other Workboook
Dim twoExcel As Excel.Application
Dim twoBook As Excel.Workbook
Dim twoSheet As Excel.Worksheet
twoExcel = New Excel.Application
twoBook = twoExcel.Workbooks.Open(pathToTwo)
twoSheet = twoBook.Worksheets(1)
'Copy over using nested for loops
For I = 1 To 10 Step 1
For J = 1 To 1000 Step 1
osheet.Cells(J, I).Value = twoSheet.Cells(J, I).Value
Next J
Next I