+ Reply to Thread
Results 1 to 3 of 3

Switching between two workbooks

Hybrid View

  1. #1
    Registered User
    Join Date
    06-09-2010
    Location
    Verwood, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Red face Switching between two workbooks

    I am a newbie trying to sort out some code. The code I cuurently have (below) works, but now I want to add to it and go back to the JnlReg workbook paste some information there from Thisfile and then save and close JnlReg.

    Thanks in advance


    Private Sub JournalReg_Click()
    Dim ThisFile As Workbook
    Set ThisFile = ThisWorkbook
    Dim Sht As Worksheet
    Dim Answer As String
    Dim JnlReg As String
     
        For Each Sht In ThisWorkbook.Worksheets
        Sht.Unprotect
        Next Sht
     
        Sheets("Jnl Vouch").Select
     
        If Company.Text = "" Then
        MsgBox "You must enter a Company"
        Exit Sub
        End If
     
        Range("rngCo") = Company.Text
        Range("rngEntry") = EntryDate
        Range("rng_pdate") = PostingDate
     
      
    ThisFile.Activate
        ActiveSheet.Calculate
        JnlReg = Range("rngFile_JnlReg").Value
        Workbooks.Open Filename:=JnlReg
     
        Sheet1.Unprotect Password:="P"
        Range("A65536").End(xlUp).Offset(1, 12).Select
     
        Answer = MsgBox("The next JV Ref is " & Selection & ". Do you want to use this JV Ref?", vbYesNo + vbQuestion)
        If Answer = vbNo Then Exit Sub
        If Answer = vbYes Then Selection.Copy
        ThisFile.Activate
        Range("rngJourn_Nos").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Switching between two workbooks

    Just create another variable to hold the opened file and assign it when you open it:

    Dim OtherFile As Workbook
    
    Set OtherFile = Workbooks.Open(Filename:=JnlReg)

    You can then refer to it as required.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    06-09-2010
    Location
    Verwood, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Switching between two workbooks

    Thank you, worked a treat

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1