OtherWorkbook function?

J

Joeslost

New Member
Joined
Jun 1, 2016
Messages
20
Hello All,
I need to copy data from one workbook to another. The catch is the source workbook has a name that will always change. The second is a static workbook.
I have a macro that uses msgboxes to prompt the user for the correct template file accomplish this goal quite well however it is confusing my enduser ( who is not very computer savy).
So I figure it is easy enough to identify the static file I can either hardcode the name or use ThisWorkbook, or even Active.Workbook with work.

But what is stumping me is adressing the "Other"Workbook. I cant always be sure its will be indexed as Workbooks(1) or 2.
My user cant reliably open them in the Correct Order.


Why Wouldn't this work?
Code: 
If ThisWorkbook = Workbooks(1) Then
    Set wb1 = Workbooks(2)
    Set wb2 = Workbooks(1)
Else
    Set wb1 = Workbooks(1)
    Set wb2 = ThisWorkbook
End If

Below is the Macro I have come up with with the help of Caleeco.

The Original Thread is here: http://www.mrexcel.com/forum/excel-...ng-between-two-excel-sheets-out-setnames.html

Code: 
Sub Import_Customer_Information()


Dim wb1 As Workbook, wb2 As Workbook
Dim answer As Integer


If Workbooks.Count > 2 Then
    MsgBox "You may only have 2 Workbooks Open when running this Macro. Exiting...", vbCritical, "Warning"
    Exit Sub
End If


If Workbooks.Count < 2 Then
    MsgBox "You need to have both your customer info sheet and templete file open! Exiting...", vbCritical, "Warning"
    Exit Sub
End If


answer = MsgBox("Is the following Workbook your Template File?  " & Workbooks(1).Name, vbYesNo + vbQuestion, "Primary Workbook")


If answer = vbYes Then
    Set wb2 = ThisWorkbook
    Set wb1 = Workbooks(1)
Else
    MsgBox "Okay, using " & Workbooks(2).Name & " as the Template File.", vbOKOnly + vbInformation, "Primary Workbook"
    Set wb1 = Workbooks(1)
    Set wb2 = Workbooks(2)
End If


    wb1.Activate
    Range("A2").Select
    Selection.Copy
    wb2.Activate
    Range("G8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    wb1.Activate
    Range("K2").Select
    Selection.Copy
    wb2.Activate
    Range("D9,J3,B13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("AC2").Select
    Selection.Copy
    wb2.Activate
    Range("J6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
   wb1.Activate
    Range("Q2").Select
    Selection.Copy
    wb2.Activate
    Range("G10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("R2").Select
    Selection.Copy
    wb2.Activate
    Range("G11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("T2").Select
    Selection.Copy
    wb2.Activate
    Range("J10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("S2").Select
    Selection.Copy
    wb2.Activate
    Range("J11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("L2").Select
    Selection.Copy
    wb2.Activate
    Range("B14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("M2").Select
    Selection.Copy
    wb2.Activate
    Range("B15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("N2").Select
    Selection.Copy
    wb2.Activate
    Range("B16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("O2").Select
    Selection.Copy
    wb2.Activate
    Range("D16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("P2").Select
    Selection.Copy
    wb2.Activate
    Range("G16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("U2").Select
    Selection.Copy
    wb2.Activate
    Range("J13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("V2").Select
    Selection.Copy
    wb2.Activate
    Range("J14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("W2").Select
    Selection.Copy
    wb2.Activate
    Range("J15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("X2").Select
    Selection.Copy
    wb2.Activate
    Range("J16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("Y2").Select
    Selection.Copy
    wb2.Activate
    Range("J17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("Z2").Select
    Selection.Copy
    wb2.Activate
    Range("K17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("AA2").Select
    Selection.Copy
    wb2.Activate
    Range("M17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("B2:B82").Select
    Selection.Copy
    wb2.Activate
    Range("B22:B102").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    wb1.Activate
    Range("F2:F82").Select
    Selection.Copy
    wb2.Activate
    Range("D22:D102").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("G2:G82").Select
    Selection.Copy
    wb2.Activate
    Range("G22:G102").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("D2:D82").Select
    Selection.Copy
    wb2.Activate
    Range("H22:H102").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("E2:E82").Select
    Selection.Copy
    wb2.Activate
    Range("J22:J102").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("I2:I82").Select
    Selection.Copy
    wb2.Activate
    Range("K22:K102").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    wb1.Activate
    Range("H2:H82").Select
    Selection.Copy
    wb2.Activate
    Range("M22:M102").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Dim rng As Range


Set rng = Range("B22:B102").SpecialCells(xlCellTypeBlanks)


rng.EntireRow.Delete


Range("B22:B102").EntireRow.Hidden = False


       
End Sub

Thanks in advance!
Joseph.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
If the macro is in the source workbook then:

Dim sBook as Workbook
Set sBook = Thisworkbook
 
Upvote 0
This should work...
Code: 
[COLOR=darkblue]If[/COLOR] ThisWorkbook [COLOR=red]Is[/COLOR] Workbooks(1) [COLOR=darkblue]Then[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wb1 = Workbooks(2)
    [COLOR=darkblue]Set[/COLOR] wb2 = ThisWorkbook
[COLOR=darkblue]Else[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wb1 = Workbooks(1)
    [COLOR=darkblue]Set[/COLOR] wb2 = ThisWorkbook
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]


Alternatively, you could prompt the user to open the template workbook with an Open File dialog.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Increasing row before the macro runs again
Replies
1
Views
107
kbk
K
brent3162
  • Solved
VBA help with increasing row before macro runs again.
Replies
3
Views
258
jolivanes
jolivanes
S
  • Question
Macro Formatting to all WS
Replies
1
Views
196
Micron
M
F
  • Solved
How to make loop stop when the cell is empty? (VBA)
Replies
3
Views
197
kvsrinivasamurthy
K
C
  • Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
363
myall_blues
myall_blues

Forum statistics

Threads
1,221,418
Messages
6,159,795
Members
451,589
Latest member
Harold14

Share this page

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
Back
Top