Pasting Information to A Separate Workbook "Inactivated

[nikolrb]

I know alot of people say this, but this board is an unmeasurable resource, thanks to everyone that pitches in.

Ok so here is what I have this week :

I am working on a project that allows the user to speed through the process of composing an estimate to our clients.

However, we are currently keeping tabs on the whole departments estimates by keeping an archive of estimates (just another excel file with 12 tabs, one for each month of the year which is how we file the estimates) and the names of the tabs are all the month ("01" for Jan and "12" for Dec) IN this "archive" we enter info about when it was provided, to whom, what it was regarding and some of the numbers.

What i want to know is...if the program that we are using to prepare our estimates could possibly "feed" this information straight to the correct tab for the month in the "archive" .xls without our having to open it and enter the info. I have seen some ways to do this but always by making the excel file you are currently working in active. Is it possible to circumvent this? I would like as little action on the screen of my users as possible, and it seems like anytime one uses the method of making something active it is slower than doing it "behind the scenes."

This is what i have below. It is a snippet from a larger routine that is triggered by pushing the "OK" button on an input form that helps the user through the composition of the estimate:

       'Saves the file under the estimate number
        Dim RefNum As String
        Const myPath = "C:\Operations\Estimates\"
        Dim Filename As String, Foldername As String
        Dim MonthFolder As String
        Dim fs As Object, chk As Boolean
        MonthFolder = Sheets("EstLetter").Range("D4").Value
        Foldername = myPath & MonthFolder & "\"
        Set fs = CreateObject("scripting.filesystemobject")
        chk = fs.folderexists(Foldername)
        If chk = False Then
            MsgBox ("There is no folder for the current month." & vbCrLf _
            & "Please contact your administrator.")
            Exit Sub
        End If
        RefNum = Sheets("ESTINFO").Range("H6").Text
        Filename = RefNum & "E.xls"
        ChDir "C:\Operations\Estimates\"
        ActiveWorkbook.SaveAs Filename:=Foldername & Filename, FileFormat:=xlNormal, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    'Hides Unused Rows
        Dim r As Range
        Dim n As Range
        Set n = Sheets("EstLetter").Range("B25:B45")
        For Each r In n
            If r.Text = "" Then
                r.EntireRow.Hidden = True
            End If
        Next r
        Set n = Sheets("EstLetter").Range("C6:C11")
        For Each r In n
            If r.Text = "" Then
                r.EntireRow.Hidden = True
            End If
        Next r
    'Saves Info For the Estimate Archive
        Dim NextRow As Long
        Dim EstDate As String, EstNum As String, Desc As String
        Dim ClientName As String, ClientCompany As String
        EstDate = Sheets("EstINFO").Range("H4").Value
        EstNum = Sheets("EstINFO").Range("H6").Value
        Desc = Sheets("EstINFO").Range("D17").Value
        ClientName = Sheets("EstINFO").Range("E4").Value & " " & Sheets("EstINFO").Range("D4").Value
        ClientCompany = Sheets("EstINFO").Range("D8").Value
'Makes PDF and Shows Where File is Saved
    Sheets("ESTLetter").PageSetup.PrintArea = "$A$1:$K$113"
    Worksheets("EstLetter").HPageBreaks.Add Before:=Range("A67")
    Sheets("EstLetter").PageSetup.BottomMargin = Application.InchesToPoints(0.25)
    Sheets("EstLetter").PageSetup.TopMargin = Application.InchesToPoints(0.25)
    Sheets("EstLetter").PageSetup.LeftMargin = Application.InchesToPoints(0.25)
    Sheets("EstLetter").PageSetup.RightMargin = Application.InchesToPoints(0.25)
    Sheets("EstLetter").PrintOut Copies:=1, ActivePrinter:="Adobe PDF", Collate:=True
    MsgBox ("Your estimate has been saved and a .pdf has been created" & vbCrLf & vbCrLf _
        & "Saved as : " & Foldername & Filename & vbCrLf & vbCrLf _
        & "Thank You!")
    'Enters estimate info into Archive
    Dim fName As String
    Dim MonthSheet As String
    MonthSheet = Sheets("Data").Range("C3").Text
    fName = "C:\Operations\Estimates\Archive2006.xls"
    Workbooks.Open Filename:=fName
        NextRow = Workbooks("ARCHIVE2006.xls").Worksheets(MonthSheet).Range("A65536").End(xlUp).Row + 1
            Cells(NextRow, 1) = EstDate
            Cells(NextRow, 2) = EstNum
            Cells(NextRow, 3) = Desc
            Cells(NextRow, 4) = ClientName
            Cells(NextRow, 5) = ClientCompany
    Workbooks("Archive2006").Close , SaveChanges = True
    'Close XL
    ActiveWorkbook.Close , SaveChanges = False