Using InputBox to navigate to path

[Masterpsyker]

I'm working on a macro right now and I've run across a problem that I don't know how to solve on my own.

The macro I am creating will be used once a year to consolidate data across workbooks and worksheets that retain a constant structure. They never change in structure, only in data.

The macro needs to do the following:

1. Create a new workbook (no problem here)
2. Open a workbook from last year (Problem)
3. Open a workbook from the current year (Problem)
4. Copy specific worksheets from the now open workbooks to the new workbook (Can I copy a whole worksheet in VBA?)
5. Run opperations (no problems here)

Items 2, and 3 are where I hit stumbling blocks. The way I want to handle opening the workbooks is to have the user of the macro interface with an input box via the "Browse" mechanic to select the path of the workbook from last year and then proceed to the next input box when the path is properly selected. The filename of each workbook needs to be saved into its own string so that it can be referred to later in the activation lines of code for copying. Workbooks should open and then be minimized.

Item 4 is something I'm wondering about. Does anyone know the VBA function to copy an entire worksheet (name and everything) from one workbook to the next?

At the end of the macro, the two opened workbooks should be closed without saving changes.

I already have user input boxes created, but do not know what to put in them for buttons and commands assigned to those buttons.

How do I do these things?

 

[Norie]

Yes post all the code.

We have no idea what wb2/1 are.

 

[Datsmart]

Do you infact have a worksheet called "Sheet1A"?
Change that sheetname to the one you want to copy to.

 

[Masterpsyker]

Here is the EXACT script below, unchanged or editted from the runtime error:

Sub SAP_TO_CX_FINAL()
' Create a new workbook
Dim wbThis
Set wbThis = Workbooks.Add
Set wbThis = ActiveWorkbook
' Variable Declarations for later
Dim R As Integer
Dim L As String
Dim string1 As String
Dim Data As String
' Dummy declarations to fill memory
R = 0
L = "string"
string1 = "string1"
Data = "Data"
' Prompt User to select the SAP sheet from THIS YEAR and open it
ChDir "G:\KCSRACCT\AAR STB Reporting"
msgBox ("Select the Opperating Expense Sheet from THIS YEAR and open it.")
Application.Dialogs(xlDialogOpen).Show
' Assigns the wbSAP reference to the SAP workbook
wbSAP = ActiveWorkbook.Name
' Routine to copy ActiveWorksheet from the SAP workbook to FinalVariance
    Workbooks(wbSAP).Sheets("R-1 Assign").Cells.Copy Destination:= _
    Workbooks(wbThis).Sheets("Sheet1").Range("A1")
' Input Box to get CX_Sheet workbook path and filename into Path and Filename strings
ChDir "G:\KCSRACCT\AAR STB Reporting"
msgBox ("Select the Variance Report from last year and open it.")
Application.Dialogs(xlDialogOpen).Show
' Assigns the wbCX reference to the CX workbook
wbCX = ActiveWorkbook.Name
' Routine to copy ActiveWorksheet from the CX_Sheet workbook to FinalVariance AND CLOSE THE CX_SHEET WHEN DONE
    Workbooks(wbCX).Sheets(1).Cells.Copy Destination:= _
    Workbooks(wbThis).Sheets("Sheet2").Range("A1")
Windows(wbCX).Activate
ActiveWorkbook.Close SaveChanges:=False
' Declare References to worksheets for later
Set wsR1 = wbThis.Worksheets("R-1 Assign")
Set wsVariance = wbThis.Worksheets("Variance Report")
Set wsStorage = wbThis.Worksheets("Sheet3")
' At this point in time, we now have a workbook that we created at the begining of the execution lines and two open workbooks from which we browsed to
' Inside our new workbook are 5 sheets: The 3 that are created by default, and the two sheets which have been copied.
' The SAP Workbook (with the R-1 assignments) worksheet will be referencing the active SAP workbook so it cannot be closed until the macro is done.
' The copied worksheet will be handled "values only" via the string copy to ActiveCell.Offset(X, X).FormulaR1C1 so that the final result in the
' FinalVariance CX_Sheet is values which are independent of any other workbook at close.
' Before we can copy the R-1 Assignments into the Variance Report, we need to run the sequence which clears current year into previous year.
' Copies CurrentYear to Storage Worksheet preserving cell format and data (and comments)
wsVariance.Activate
For X = 6 To 21 Step 5
    Range(Cells(16, X), Cells(97, X)).Copy Destination:=wsStorage.Range(Cells(16, X), Cells(97, X))
    Range(Cells(101, X), Cells(118, X)).Copy Destination:=wsStorage.Range(Cells(101, X), Cells(118, X))
    Range(Cells(121, X), Cells(138, X)).Copy Destination:=wsStorage.Range(Cells(121, X), Cells(138, X))
    Range(Cells(141, X), Cells(163, X)).Copy Destination:=wsStorage.Range(Cells(141, X), Cells(163, X))
    Range(Cells(168, X), Cells(185, X)).Copy Destination:=wsStorage.Range(Cells(168, X), Cells(185, X))
    Range(Cells(188, X), Cells(202, X)).Copy Destination:=wsStorage.Range(Cells(188, X), Cells(202, X))
    Range(Cells(205, X), Cells(209, X)).Copy Destination:=wsStorage.Range(Cells(205, X), Cells(209, X))
    Range(Cells(212, X), Cells(221, X)).Copy Destination:=wsStorage.Range(Cells(212, X), Cells(221, X))
    Range(Cells(224, X), Cells(232, X)).Copy Destination:=wsStorage.Range(Cells(224, X), Cells(232, X))
    Range(Cells(236, X), Cells(253, X)).Copy Destination:=wsStorage.Range(Cells(236, X), Cells(253, X))
Next X
'Clears the PriorYear and any comments attached to those cells
For X = 7 To 22 Step 5
    Range(Cells(16, X), Cells(97, X)).Clear
    Range(Cells(101, X), Cells(118, X)).Clear
    Range(Cells(121, X), Cells(138, X)).Clear
    Range(Cells(141, X), Cells(163, X)).Clear
    Range(Cells(168, X), Cells(185, X)).Clear
    Range(Cells(188, X), Cells(202, X)).Clear
    Range(Cells(205, X), Cells(209, X)).Clear
    Range(Cells(212, X), Cells(221, X)).Clear
    Range(Cells(224, X), Cells(232, X)).Clear
    Range(Cells(236, X), Cells(253, X)).Clear
Next X
' Returns the previously stored information to it's new home in the prior year (with comments still attached)
wsStorage.Activate
For X = 6 To 21 Step 5
    Range(Cells(16, X), Cells(97, X)).Copy Destination:=wsVariance.Range(Cells(16, X), Cells(97, X))
    Range(Cells(101, X), Cells(118, X)).Copy Destination:=wsVariance.Range(Cells(101, X), Cells(118, X))
    Range(Cells(121, X), Cells(138, X)).Copy Destination:=wsVariance.Range(Cells(121, X), Cells(138, X))
    Range(Cells(141, X), Cells(163, X)).Copy Destination:=wsVariance.Range(Cells(141, X), Cells(163, X))
    Range(Cells(168, X), Cells(185, X)).Copy Destination:=wsVariance.Range(Cells(168, X), Cells(185, X))
    Range(Cells(188, X), Cells(202, X)).Copy Destination:=wsVariance.Range(Cells(188, X), Cells(202, X))
    Range(Cells(205, X), Cells(209, X)).Copy Destination:=wsVariance.Range(Cells(205, X), Cells(209, X))
    Range(Cells(212, X), Cells(221, X)).Copy Destination:=wsVariance.Range(Cells(212, X), Cells(221, X))
    Range(Cells(224, X), Cells(232, X)).Copy Destination:=wsVariance.Range(Cells(224, X), Cells(232, X))
    Range(Cells(236, X), Cells(253, X)).Copy Destination:=wsVariance.Range(Cells(236, X), Cells(253, X))
Next X
' R (for right-hand) is the number that is read off the R-1 Assign Coulmn "A" string and stored to search for the Excel row
' L (for letter) is the character returned by the Right function and used to position the Excel Column
' Data is the variable which will hold the numbers from the SAP wkbk until they are transfered to the CX Sheet
' Activate the SAP Operational Expense SHEET @ Cell A3
wsR1.Range("A3").Activate
' Activate loop for data input
counter = 0
Do
    ' Establish Data from adjacent cell, read string in A as two parts (Number + Letter)
    Data = ActiveCell.Offset(0, 1).Text
    R = Val(ActiveCell.Formula)
    L = Right(ActiveCell.Formula, 1)
    ' Activate Cx Sheet for searching and input
    wsVariance.Range("B17").Activate
    'Loop to find correct row according to number R
    Do
        If ActiveCell.Formula = R Then
            Exit Do
        Else
            ActiveCell.Offset(1, 0).Activate
            End If
    Loop
    If L = "B" Then
        ActiveCell.Offset(0, 4).FormulaR1C1 = Data
    ElseIf L = "C" Then
        ActiveCell.Offset(0, 9).FormulaR1C1 = Data
    ElseIf L = "D" Then
        ActiveCell.Offset(0, 14).FormulaR1C1 = Data
    ElseIf L = "E" Then
        ActiveCell.Offset(0, 19).FormulaR1C1 = Data
    End If
    counter = counter + 1
    wsR1.Range("A3").Activate
    ActiveCell.Offset(counter, 0).Activate
    string1 = ActiveCell.Offset(1, 0).Formula
    If string1 = "" Then
        Exit Do
    End If
Loop
Windows(wbSAP).Activate
ActiveWorkbook.Close SaveChanges:=False
End Sub

I noticed that I wasn't using the "SET" command to set the references to the wbSAP and wbCX objects, so I revised that in my code... do I also need to use the Dim command to set up the wbSAP and wbCX as objects?