View Full Version : Solved: Select a Sheet in another Workbook
I have some code that opens another Workbook and copies the used range.
Below is a few lines of the code that does this.
Can the Range line be modified to select a specific worksheet to copy from?
Thanks...
JimS
' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.*")
' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub
' Set an object variable for the workbook containing the input file.
Set SourceBook = ActiveWorkbook
' Copy the contents of the entire sheet containing the text file.
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
ThisWorkbook.Worksheets("MySheet").UsedRange.Copy
That selected the wrong workbook.
This worked:
ActiveWorkbook.Worksheets("Test_Data_1").UsedRange.Copy
Do you know if this can be changed to reference the sheet by it's Property Name (ie: Sheet1) and not it's Tab Name?
halimi1306
04-20-2011, 08:14 AM
Hi there, this code lets u choose specific file
Code:
Sub copysheets()
Dim wbCopy AsWorkbook
Dim wbPaste AsWorkbook
Dim wsCopy AsWorksheet
Set wbPaste = ThisWorkbook
Dim x AsInteger
Dim arrFiles
arrFiles = svpth2
If IsEmpty(arrFiles) = True Then
MsgBox ("please select some files")
Exit Sub
End If
For x = 0 ToUBound(arrFiles)
If arrFiles(x) <>ThisWorkbook.FullName Then
SetwbCopy = Workbooks.Open(arrFiles(x))
ForEach wsCopy In wbCopy.Sheets
wsCopy.Copyafter:=wbPaste.Sheets(wbPaste.Sheets.Count)
wbCopy.Close (False)
NextwsCopy
End If
Next x
End Sub
Private Function svpth2() As Variant
Dim fd AsFileDialog
Set fd =Application.FileDialog(msoFileDialogFilePicker)
Dim strSelectedItem As String
Dim f, x, files
x = 0
With fd
.AllowMultiSelect = True
.Title = "please choose files"
.Filters.Add "Excel Workbooks", "*.xls; *.xlsx; *.xlsb;*.xlsm", 1
If .Show = -1 Then
ReDim files(.SelectedItems.Count -1)
For Eachf In .SelectedItems
files(x) = f
x = x + 1
Next
svpth2 = files
End If
End With
End Function
That selected the wrong workbook.
This worked:
ActiveWorkbook.Worksheets("Test_Data_1").UsedRange.Copy
Do you know if this can be changed to reference the sheet by it's Property Name (ie: Sheet1) and not it's Tab Name?
Presuming you mean a worksheet in another workbook (another wb vs. the wb the code is in), no, not directly. Think of a sheet's codename sort of like ThisWorkbook or Me under a sheet or userform.
You can of course loop through the worksheets and IF wks.CodeName="Whatever"...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.