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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
245
willow1985
willow1985
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
189
kbk
K
J
  • Solved
Continuous Copy and paste
2
Replies
10
Views
167
Joe4
Joe4
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
181
jolivanes
jolivanes
brent3162
  • Solved
VBA help with increasing row before macro runs again.
Replies
3
Views
343
jolivanes
jolivanes

Forum statistics

Threads
1,223,247
Messages
6,171,004
Members
452,374
Latest member
keccles

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