Copying range from selected workbook to open workbook

[John]

I am using the following to create worksheets but each month, I need to
carry forword the last dated information to the new month. The last
information will be a constant range on the last week of the month.
(.count -1 ). The copy to is a constant range on the first worksheet. I
have "highlighted" the area I need help on. Everythng else works. I can
select the file but it does not open. The file does not need to be opened
per say, just that range need to copied and pasted in to the current
Worksheet.

Thanks for the help!
John

Private Sub Copysheet_Click()
Dim iReply As Integer
iReply = MsgBox(Prompt:="Do you want to start a new week?", _
Buttons:=vbYesNoCancel, Title:="Copy Sheet")
If iReply = vbYes Then
Dim wSht As Worksheet
Dim shtName As String
Sheets("master").Range("AG1").Value = InputBox(("Enter the Start
Date: (mm/dd/yy)"), "Week starting date")
shtName = Sheets("master").Range("AH1")
For Each wSht In Worksheets
If wSht.Name = shtName Or shtName = "" Or IsNumeric(shtName)
Then
MsgBox "Sheet already exists or name is invalid",
vbInformation
Exit Sub
End If
Next
Dim CntSheets, CntSheetsPrev As Long
CntSheets = Application.Sheets.Count
CntSheetsPrev = Application.Sheets.Count - 1

If CntSheets = 1 Then
Sheets(1).Copy before:=Sheets(1)
Sheets(1).Name = shtName
Sheets(1).Range("B6").Select

'***************************************
Dim FName As Variant
FName = Application.GetOpenFilename("Excel Workbooks (*.xls),
*.xls")
Application.Range("B216:Y242").Select
Selection.Copy
Sheets(CntSheets).Select
Application.Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
'*******************************************
Else
Sheets("master").Copy After:=Sheets(CntSheetsPrev)
shtName = Sheets("master").Range("AH1")
Sheets(CntSheets).Name = shtName
Sheets(CntSheetsPrev).Select
Range("B216:Y242").Select
Selection.Copy
Sheets(CntSheets).Select
Application.Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

End If

ElseIf iReply = vbNo Then
MsgBox "Oh Bummer"
Else 'They cancelled (VbCancel)
Exit Sub
End If
Sheets(CntSheets).Select
End Sub

 

[Joel]

When you open the workbook the focus changes from the origianl workbook to
the open workbook. Your problem is you not referencing the old workbook.
Try this new code

'***************************************
Dim FName As Variant
oldworkbookname = thisworkbook.name
FName = Application.GetOpenFilename( _
"Excel Workbooks (*.xls), *.xls")
workbooks(oldworkbookname).worksheets(shtname). _
Range("B216:Y242").copy _
destination:=Sheets(CntSheets).Range("B6")

'*******************************************

 

[John]

Thanks Joel. That did it.

John

When you open the workbook the focus changes from the origianl workbook to
the open workbook. Your problem is you not referencing the old workbook.
Try this new code

'***************************************
Dim FName As Variant
oldworkbookname = thisworkbook.name
FName = Application.GetOpenFilename( _
"Excel Workbooks (*.xls), *.xls")
workbooks(oldworkbookname).worksheets(shtname). _
Range("B216:Y242").copy _
destination:=Sheets(CntSheets).Range("B6")

'*******************************************