Solved: Select a Sheet in another Workbook

[JimS]

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

[vba]

' 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

[/vba]

[GTO]

[vba]ThisWorkbook.Worksheets("MySheet").UsedRange.Copy[/vba]

[JimS]

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]

Hi there, this code lets u choose specific file

Code:
[VBA]
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
[/VBA]

[GTO]

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"...

[JimS]

Got it Thanks...