OK, assuming you want what's in...
Productivity!B1:E10 to be pasted into Import Pimms! upper left cell J5
and what's in...
Volumes Received!B1:E20 to be pasted into Import Pimms! upper left cell R5
then...
Please follow these directions exactly as I write them.
Step 1
Start by closing all workbooks in Excel except for the workbook that you want this functionality for, that is, your Import workbook that holds the Import Pimms worksheet.
Step 2
In this lone open workbook, from any worksheet press Alt+F11 to go into the Visual Basic Editor. Careful, that's ALT + F11.
Step 3
Hit Ctrl+R.
Step 4
In the Project - VBA Project window that is in the vertical pane on the left, find your workbook name in bold font. It will look like VBAProject (YourImportWorkbookName.xls)
Step 5
Expand the yellow Objects folder.
Step 6
Right click on the object named ThisWorkbook, and left click to select ViewCode.
Step 7
Paste this code into the large white window pane that is the workbook module:
Code:
Private Sub RightKlickMenuReeset()
On Error Resume Next
Application.CommandBars("wbNavigator").Delete
Err.Clear
End Sub
Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, wb As Workbook
Run "RightKlickMenuReeset"
Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)
For Each wb In Workbooks
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & " (this workbook)", wb.Name)
.Style = msoButtonCaption
.OnAction = "wbExport"
End With
Next wb
End Sub
Private Sub wbExport()
Dim strNameWB$, intAnswer%
strNameWB = Workbooks(Application.Caller(1)).Name
intAnswer = MsgBox("Import data from " & strNameWB & "?", 36, "Please confirm...")
If intAnswer = 7 Then
MsgBox "No problem, nothing will import.", 64, "You clicked No."
Exit Sub
End If
Application.ScreenUpdating = False
With Workbooks(strNameWB)
.Worksheets("Productivity").Range("B1:E10").Copy _
ThisWorkbook.Worksheets("Import Pimms").Range("J5")
.Worksheets("Volumes Received").Range("B1:E20").Copy _
ThisWorkbook.Worksheets("Import Pimms").Range("R5")
End With
Application.ScreenUpdating = True
MsgBox "Import complete.", 64, "Make time for beer."
End Sub
Step 8
While you are in the VBE, from the menu bar at the top, click Insert > Module, and paste this code into that new module:
Code:
Private Sub RightKlickMenuReeset()
On Error Resume Next
Application.CommandBars("wbNavigator").Delete
Err.Clear
End Sub
Private Sub RightKlickMenuMayker()
Dim objBar As Object, objBtn As Object, wb As Workbook
Run "RightKlickMenuReeset"
Set objBar = Application.CommandBars.Add("wbNavigator", msoBarPopup)
For Each wb In Workbooks
Set objBtn = objBar.Controls.Add
With objBtn
.Caption = IIf(wb.Name = ThisWorkbook.Name, wb.Name & " (this workbook)", wb.Name)
.Style = msoButtonCaption
.OnAction = "wbExport"
End With
Next wb
End Sub
Private Sub wbExport()
Dim strNameWB$, intAnswer%
strNameWB = Workbooks(Application.Caller(1)).Name
intAnswer = MsgBox("Import data from " & strNameWB & "?", 36, "Please confirm...")
If intAnswer = 7 Then
MsgBox "No problem, nothing will import.", 64, "You clicked No."
Exit Sub
End If
Application.ScreenUpdating = False
With Workbooks(strNameWB)
.Worksheets("Productivity").Range("B1:E10").Copy _
ThisWorkbook.Worksheets("Import Pimms").Range("J5")
.Worksheets("Volumes Received").Range("B1:E20").Copy _
ThisWorkbook.Worksheets("Import Pimms").Range("R5")
End With
Application.ScreenUpdating = True
MsgBox "Import complete.", 64, "Make time for beer."
End Sub
Step 9
Press Alt+Q.
Step 10
Save your workbook.
Step 11
Close your workbook.
Step 12
Open your workbook and any other workbooks, and when your workbook is activate, from any worksheet, you can right click a cell to see a pop up list of other open workbooks. Left click to select the workbook name you want to activate.
Note that the custom right click menu will only appear if more than one workbook is open in that same instance of Excel, and it will only appear in the subject workbook where the code is housed.
ha!
