unable to reactivate workbook using activate..want to copy from 1 worksheet to another workbook

Question

The below code is giving Subscript out of range error at the highlighted line ..Not sure why ..Please Help

    Private Sub CommandButton1_Click()
    Dim New_Book As String
    Dim Address As String
    Dim Source As String
    Dim Version As String
    Dim NWkbook As Workbook
    Dim WkSheet1, WkSheet2, WkSheet3 As Worksheet
    Application.SheetsInNewWorkbook = 1
   'WkSheet2.Name = "Scenarios"
   'WkSheet3.Name = "Considerations"



   Version = "1"
   Source = ActiveWorkbook.FullName
   'MsgBox (Source)
    Address = Application.ActiveWorkbook.Path
    New_Book = Address + "\" & Worksheets("Test Scenarios").Range("A2") +      "_SIT_Scenarios_V" + Version + ".0"


    'MsgBox (Dir(New_Book + "*"))
    'thesentence = InputBox("Type the filename with full extension", "Raw Data File")
    If Len(Dir(New_Book + "*")) = 0 Then

    Else
    Do
        Version = Version + 1
        New_Book = Address + "\" & Worksheets("Test Scenarios").Range("A2") + "_SIT_Scenarios_V" + Version + ".0"
    Loop Until Dir(New_Book + "*") = ""

 End If
    'MsgBox (New_Book)
    New_Book = New_Book + ".xlsx"


   Set NWkbook = Workbooks.Add
   NWkbook.Worksheets("Sheet1").Name = "Considerations"

   Set WkSheet2 = Sheets.Add
   WkSheet2.Name = "Scenarios"

Set WkSheet1 = Sheets.Add
WkSheet1.Name = "Estimation"


NWkbook.SaveAs New_Book

'MsgBox (Source)



***Workbooks(Source).Activate***


'Workbooks(Source).Worksheets("Test Scenarios").Select
'Workbooks(NWkbook).Worksheets("Scenarios").Range("A1").Paste

End Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:03

Try to change line Source = ActiveWorkbook.FullName to Source = ActiveWorkbook.Name - it will fix an error.

But using following construction:

Source = ActiveWorkbook.Name
'your code
Workbooks(Source).Activate

is not good way of programming. Try to change it to the following construction (avoid using Select and Activate statements in your code. Here is a very good explanation why you shoudn't use them and what you should use instead):

Dim thisWb As Workbook
Set thisWb = ThisWorkbook
'your code
thisWb.Sheets("Sheet1").Range("A1")="newValue"

And the last thing: when you delcare variables like this

Dim WkSheet1, WkSheet2, WkSheet3 As Worksheet

only WkSheet3 is object of type Worksheet, WkSheet1 and WkSheet2 are Variant. You should use following declaration:

Dim WkSheet1 As Worksheet, WkSheet2 As Worksheet, WkSheet3 As Worksheet

thnx simoco...for a quick response and suggestions ..it worked — SSR, Jan 14 '14 at 06:26

unable to reactivate workbook using activate..want to copy from 1 worksheet to another workbook

1 Answers1