Windows("Book1").Activate Any other way?

[zone709]

Hi after running macro to add a workbook. Sometimes the book changes from book1 to book2 or book3 etc. Then the book is selected and if the number is wrong it will Debug.

I need to fix this to maybe just read book and not the number. Windows("Book1").Activate - I tried this but nothing. Windows("Book*").Activate

 

[MARK858]

You can try something like the code below (although personally I wouldn't use a wildcard to activate files)

Sub LoopOpenWb()
    Dim wb As Workbook, cntr As Long

    For Each wb In Application.Workbooks
    
        If wb.Name Like "Book*" Then
            cntr = cntr + 1
            wb.Activate
            Exit For
        End If

    Next wb

    If cntr = 0 Then MsgBox "No file to open"

End Sub

 

[zone709]

Hi all i really want to do before i button this up is change these line if i can.

Windows("Book7").Activate ' This keeps changing need to just read Book only' Because i want to keep sheet open and keep running it. If i keep running this it goes to
book8 book9 book10 and breaks all the time.

Sub Run101()




    Workbooks.Add
    Windows("TimeSheet Formula.xlsx").Activate
    Cells.Copy
    Windows("Book7").Activate ' This keeps changing need to just read Book'
    Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Cells.EntireColumn.AutoFit
    Columns("M:M").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("N12").Select
End Sub

 

[dreid1011]

Assuming you are pasting the data from "Timesheet Formulas.xlsx" into the new book? Untested, but maybe this:

Sub Run101()



Dim wbNew As Workbook
Set wbNew = Workbooks.Add
    Windows("TimeSheet Formula.xlsx").Activate
    Cells.Copy
    wbNew.Activate ' This keeps changing need to just read Book'
    Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Cells.EntireColumn.AutoFit
    Columns("M:M").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("N12").Select
End Sub

 

[zone709]

Thank you. I will try this later

 

[petertenthije]

If you createa a new workbook, then it is by default the active worbook.
With that in mind, you can ActiveWorkbook.Name to identify the workbook, and go on from there.

Sub Run101()
    Workbooks.Add
    NewWorkbook = Activeworkbook.Name           ' Note this change to identify the new workbook name
    Windows("TimeSheet Formula.xlsx").Activate
    Cells.Copy
    Windows(NewWorkbook).Activate                     ' Note how the file no longer refers to the actual workbook name, but to the variable
    Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Cells.EntireColumn.AutoFit
    Columns("M:M").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("N12").Select
End Sub

And tweaking your macro a bit further to speed it up:

Sub Run101()
' Identify variables for the source file. (workbook name, worksheet name, last cell with data)
    Source_WB = Activeworkbook.Name
    Source_WS = ActiveSheet.Name
    With activesheet  
        Source_lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End with

' Add workbook
    Workbooks.Add

' Identify variables for the new file. (workbook name, worksheet name)
    New_WB = Activeworkbook.Name
    New_WS = ActiveSheet.Name

' Transfer data from source to new workbook.
' This assumes your data goes from A through M.
' You'll need to modify it if there are more columns.
    Workbooks(New_WB).Sheets(New_WS).Range("A1:M" & Source_lastrow) = Workbooks(Source_WB).Sheets(Source_WS).Range("A1:M" & Source_lastrow).value

' Modifying column width.
' Again, assuming data ends at column M.
    Workbooks(New_WB).Sheets(New_WS).Columns("A:M").AutoFit

' Modify formatting
    Workbooks(New_WB).Sheets(New_WS).Range("M1:M" & Source_lastrow).NumberFormat = "m/d/yyyy"

End Sub

 

[zone709]

thank you guys will try later.

 

[zone709]

Works great. Thank you guys for the help.