Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste whole worksheet from one workbook to another
I need to copy an entire worksheet from one workbook to another. Currently,
i am using the following code: Set objExcel = CreateObject("Excel.Application") set objWorkbook = objExcel.Workbooks.Open(Filename1) set objWorksheet = objworkbook.worksheets(1) objExcel.displayalerts=false Set objRange = objWorksheet.UsedRange objRange.copy set objworkbook = objExcel.workbooks.open(filename2) set objworksheet = objworkbook.worksheets("FS Data") Set objRange2 = objexcel.Range("A1") objrange2.activate objworksheet.paste objworkbook.save My data gets there, but only if i never edit the destination sheet or even type on it. I need my copy to always start in A1. It does not. the copy will start from whereever the cursor was last on that sheet when the file was saved. I have to start sharing the script and the file with other users who will not be as careful as i will. -- Allen Whitelock Systems Administrator World Class Automotive |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste whole worksheet from one workbook to another
Try the below
Set objExcel = CreateObject("Excel.Application") set objWorkbook1 = objExcel.Workbooks.Open(Filename1) set objworkbook2 = objExcel.workbooks.open(filename2) objworkbook1.worksheets(1).UsedRange.Copy _ objworkbook2.worksheets("FS Data").Range("A1") objworkbook2.save -- Jacob "Allen the Computer Guy" wrote: I need to copy an entire worksheet from one workbook to another. Currently, i am using the following code: Set objExcel = CreateObject("Excel.Application") set objWorkbook = objExcel.Workbooks.Open(Filename1) set objWorksheet = objworkbook.worksheets(1) objExcel.displayalerts=false Set objRange = objWorksheet.UsedRange objRange.copy set objworkbook = objExcel.workbooks.open(filename2) set objworksheet = objworkbook.worksheets("FS Data") Set objRange2 = objexcel.Range("A1") objrange2.activate objworksheet.paste objworkbook.save My data gets there, but only if i never edit the destination sheet or even type on it. I need my copy to always start in A1. It does not. the copy will start from whereever the cursor was last on that sheet when the file was saved. I have to start sharing the script and the file with other users who will not be as careful as i will. -- Allen Whitelock Systems Administrator World Class Automotive |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste whole worksheet from one workbook to another
Hi Allen
This will open a designated workbook paste the data from the sheet you run it from, close the workbook saving the changes. You will have to adjust the sheet name, the file path and the copy range. Take care Marcus Option Explicit Sub OpenXL() Dim oWbk As Workbook Dim sFil As String Dim sPath As String Dim twbk As Workbook Dim lr As Integer Dim lw As Integer Dim strFullName As String Set twbk = ActiveWorkbook Application.DisplayAlerts = False Application.ScreenUpdating = False lw = Range("A" & Rows.Count).End(xlUp).Row twbk.Sheets("Sheet1").Range("A2:A" & lw).Copy sPath = "R:\" 'Cell B2 of Cal sheet, location of files ChDir sPath sFil = Dir("Test.xls") 'change or add formats strFullName = sPath & sFil Set oWbk = Workbooks.Open(strFullName) lr = oWbk.Sheets("FS Data").Range("A" & Rows.Count).End(xlUp).Row + 1 oWbk.Sheets("FS Data").Range("A" & lr).PasteSpecial xlPasteValues oWbk.Close True 'close the workbook, saving changes sFil = Dir End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste whole worksheet from one workbook to another
This worked perfectly and was too easy. I am kicking myself for not figuring
this one out on my own. Thanks. -- Allen Whitelock Systems Administrator World Class Automotive "Jacob Skaria" wrote: Try the below Set objExcel = CreateObject("Excel.Application") set objWorkbook1 = objExcel.Workbooks.Open(Filename1) set objworkbook2 = objExcel.workbooks.open(filename2) objworkbook1.worksheets(1).UsedRange.Copy _ objworkbook2.worksheets("FS Data").Range("A1") objworkbook2.save -- Jacob "Allen the Computer Guy" wrote: I need to copy an entire worksheet from one workbook to another. Currently, i am using the following code: Set objExcel = CreateObject("Excel.Application") set objWorkbook = objExcel.Workbooks.Open(Filename1) set objWorksheet = objworkbook.worksheets(1) objExcel.displayalerts=false Set objRange = objWorksheet.UsedRange objRange.copy set objworkbook = objExcel.workbooks.open(filename2) set objworksheet = objworkbook.worksheets("FS Data") Set objRange2 = objexcel.Range("A1") objrange2.activate objworksheet.paste objworkbook.save My data gets there, but only if i never edit the destination sheet or even type on it. I need my copy to always start in A1. It does not. the copy will start from whereever the cursor was last on that sheet when the file was saved. I have to start sharing the script and the file with other users who will not be as careful as i will. -- Allen Whitelock Systems Administrator World Class Automotive |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste Worksheet to End of Workbook | Excel Programming | |||
Copy and Paste Chart as Picture into another workbook or worksheet | Charts and Charting in Excel | |||
copy data from one worksheet and paste into another workbook | Excel Programming | |||
Copy worksheet and paste it in New workbook, all data except Formulas | Excel Programming | |||
Copy and paste the worksheet to New workbook.. | Excel Programming |