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" wrote:
> 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
>
>
>
|
Similar Threads
