Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a specific sheet by codename in an opened workbook (XL03
I have a master data workbook, and will be pulling in data from source XL files from various locations. At the moment, I'm trying to create a function that will allow me to pass a location (workbook to pull), and specify which sheet to grab from the source workbook, and which sheet in the master data workbook to past that sheet into. I'm having some trouble (error: does not support this property or method) specifying the source sheet in the newly opened source workbook- can anyone give me some pointers? (I replaced path/filename to shorten the code line below in the test sub, but the path is valid and it does open the correct file) Thank you!! Keith Sub test zz= PullAllRawData("C:\path\filename.xlsx",Sheet1,Shee t13) end sub Function PullAllRawData(MyFullFilePath As String, _ SourceSheet As Worksheet, _ DestSheet As Worksheet) Dim i As Integer Dim owb As Workbook 'original/main Dim twb As Workbook 'temp/data file Dim ows As Worksheet Dim tws As Worksheet DestSheet.Activate Set owb = ActiveWorkbook Set ows = ActiveWorkbook.ActiveSheet 'clear the destination sheet to make sure there isn't leftover old data ows.Cells.Clear Application.StatusBar = "Opening File " & MyFullFilePath 'Open source workbook Set twb = Workbooks.Open(FileName:=MyFullFilePath) twb.Activate twb.SourceSheet.Activate '<<<< this is where it errors out 'grab the data twb.SourceSheet.Cells.Select Selection.Copy ows.Activate ows.Range("A1").Select ActiveSheet.Paste 'Select/copy a single cell to avoid clipboard warnings ActiveSheet.Range("A1").Copy 'close the workbook to get it out of the way Application.DisplayAlerts = False 'just in case the clipboard trick doesn't work twb.Close SaveChanges:=False Application.DisplayAlerts = True End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a specific sheet by codename in an opened workbook (XL03
Try setting the sheet name as a string and then call it like this
mysheet = "Sheet3" Sheets(mysheet).Select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a specific sheet by codename in an opened workbook (
Hi John-
I'll give that a try, but I suspect that this method uses the "user" name of the sheet, rather than the "code" sheetname. The source workbook is coming from another department, and I won't have an control over whether they reorder the sheets (e.g. change the index number) or rename the sheet through the GUI- I was hoping to use just the code sheetname to avoid either scenario. I'll give it a shot, and we'll see what happens. Thanks, Keith "John Bundy" wrote: Try setting the sheet name as a string and then call it like this mysheet = "Sheet3" Sheets(mysheet).Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using CodeName when Selecting Multi-Tabs | Excel Programming | |||
Selecting Sheet By Codename | Excel Programming | |||
selecting specific columns with data and put it in a new sheet | Excel Programming | |||
load event when specific workbook is opened | Excel Programming | |||
Selecting a sheet by codename | Excel Programming |