copy worksheet from inactive workbook
I want to copy an entire worksheet from an inactive workbook without opening
it. Would someone be so kind as to talk me through this? For more background, I am having the user enter a list of file names; I want to automatically copy a certain worksheet (same name each workbook) from each of the listed workbooks into the active workbook. I have code working that copies the names of the listed workbooks into a string array (not sure if that was the way to go or not). |
copy worksheet from inactive workbook
You're going to need to open the workbook in order to copy the worksheet.
You could open it as READ ONLY. You could try something liket this: Dim oWB As Workbook Dim oWS As Worksheet Dim aWB As Workbook Set aWB = activebook Dim Filename As String Filename = aws.Cells(1, 1).Value '<~~~ change this It should be something like C:\Documents and Settings\Customer\My Documents\filename.xls Set oWB = Workbooks.Open(Filename, UpdateLinks:=False, ReadOnly:=True) On Error Resume Next Set oWS = oWB.Worksheet("Sheet1") '<~~~~CHANGE THE SHEET NAME HERE On Error GoTo 0 If Not oWS Is Nothing Then oWS.Copy AFTER:=aWB.Sheets(aWB.Worksheets.Count) End If "br549" wrote: I want to copy an entire worksheet from an inactive workbook without opening it. Would someone be so kind as to talk me through this? For more background, I am having the user enter a list of file names; I want to automatically copy a certain worksheet (same name each workbook) from each of the listed workbooks into the active workbook. I have code working that copies the names of the listed workbooks into a string array (not sure if that was the way to go or not). |
All times are GMT +1. The time now is 08:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com