Saving Active Workbook as Variable

**amyxkatexx** · 03-09-2010, 02:56 PM

Okay, so I have quite a few scenarios where I am needing within my macro to activate multiple workbooks.

These workbooks are often named different names, including one of which I use regularly that is a template, so every time it opens, it has a number at the end of it.

I had an epiphany that if there was a way that I could get excel to assign a variable name to an active workbook so that later in the code, it is able to go back to that workbook that is no longer active now because it has moved on to other workbooks since then, that would make life a whole lot easier.

I googled a bit, but all I could find was code that activates workbooks.

So in a nutshell, I am not needing a code that can activate a workbook. I need a code that can sort of grab whatever the name is of the current active workbook and allow me to use that later.

Any ideas?

**JBeaucaire** · 03-09-2010, 04:08 PM

Like so:

Dim wbTemp as Workbook

Workbooks.Open "Template.xls"

Set wbTemp = ActiveWorkbook

With this, you can use wbTemp as the template object through the rest of the macro.

**amyxkatexx** · 03-09-2010, 04:44 PM

Okay, so right now I use this code to open the workbook.

Workbooks.Open ("Lorencz:Users:amykate:Desktop:Template.xlt")

I have some code before it and then more code after it. But at the end, I am wanting to go back to that open template to use it again.

Where would I put that "Dim wbTemp as Workbook" part?

Would it be like this? (your code is in bold)

Sub FTH()

    Dim wbTemp as Workbook

    Selection.Columns("A:BQ").Copy

    Workbooks.Open ("Lorencz:Users:amykate:Desktop:WebconTemplate.xlt")

    Sheets("FTH").Select
    Range("A2").Select
    
    Selection.PasteSpecial Paste:=xlValues
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

    Set wbTemp = ActiveWorkbook

    Sheets("FTH").Select
    Range("A1").Select
    
   Cells.Find(What:="STOP", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
      :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
      MatchCase:=False).Activate

    Range(ActiveCell, "BR2").Copy
    Workbooks("2010_RunningSheet.xls").Worksheets("FTH").Cells _
        (Rows.Count, "C").End(xlUp).Offset(1, -2).PasteSpecial xlPasteValues

End Sub

And also, what would the activate workbook code look like using that object?

Like this?

Workbooks("wbTemp").Activate

**JBeaucaire** · 03-09-2010, 08:36 PM

Maybe like so...the wbTemp is an object, so you can use it as such, no need to try and nest it inside an Sheet reference.

Option Explicit

Sub FTH()
Dim wbTemp As Workbook

Workbooks.Open ("Lorencz:Users:amykate:Desktop:Template.xlt")
Set wbTemp = ActiveWorkbook

    Columns("A:BQ").Copy
    
    Workbooks.Open ("Lorencz:Users:afarrow:Desktop:WebconTemplate.xlt")
    
    Sheets("FTH").Activate
    Range("A2").PasteSpecial Paste:=xlValues
    Cells.Value = Cells.Value
    
   Cells.Find(What:="STOP", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
      :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
      MatchCase:=False).Activate

    Range(ActiveCell, "BR2").Copy
    
    Workbooks("2010_RunningSheet.xls").Worksheets("FTH").Cells _
        (Rows.Count, "C").End(xlUp).Offset(1, -2).PasteSpecial xlPasteValues

wbTemp.Activate

End Sub

**royUK** · 03-10-2010, 02:27 AM

You generally do not need to activate workbooks,sheets or ranges

If your workbook is actually a template (*.xlt) then when it opens it creates a new workbook (*.xls) based on the template. So each time your code opens the template a new workbook will be created so it will need renaming.

Just a guess from your posted code

Option Explicit

Sub FTH()
    Dim wbTemp As Workbook
    Dim wbThis As Workbook
    Dim cl As Range

    Set wbThis = ThisWorkbook
    ActiveSheet.Columns("A:BQ").Copy


    Set wbTemp = Workbooks.Open("Lorencz:Users:afarrow:Desktop:WebconTemplate.xlt")

    With wbTemp
        .Sheets("FTH").Range("A2").Selection.PasteSpecial Paste:=xlValues
        .Cells.Select
        .Selection.Copy
        .Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                False, Transpose:=False

        Set cl = .UsedRange.Find(What:="STOP", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                                    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                                 MatchCase:=False)
        If Not cl Is Nothing Then
            .Range(cl, "BR2").Copy
            Workbooks("2010_RunningSheet.xls").Worksheets("FTH").Cells _
                    (Rows.Count, "C").End(xlUp).Offset(1, -2).PasteSpecial xlPasteValues
        End If
    End With
End Sub