Pasting Information to A Separate Workbook "Inactivated

nikolrb

New Member
Joined
Apr 1, 2005
Messages
19
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:


Code:
       '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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,141,587
Messages
5,707,247
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top