Macros: Switching between two excel sheets with out setnames.

J

Joeslost

New Member
Joined
Jun 1, 2016
Messages
20
Hello All. My name is Joe. I consider myself an intermediate user of excel. I do NOT speack any Visual Basic.
I am trying to create, what I feel Should be a simple Macro but I am stumbling over one detail.
I need to copy information from one Workbook to another. But I need the macro to be applicable to any two workBooks laid out in the same way. ( I have a program that exports data in the same format every time, and I have multiple different templates laid out the same to copy the data into).
Below is the macro excel generated for me, and works very well, but will only work for the documents hard coded into it.

Thanks In Advance!!
Joe
**I apologize if this has been addressed before, I've found a few threads around the internet on similar topics, but none of the solutions seemed to help me**

Code: 
Sub Transfer()'
' Transfer Macro
'
' Keyboard Shortcut: Ctrl+t
'
    Windows("orchard market fruitport.xls").Activate
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("G8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("K2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    Range("Q2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("G10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("R2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("G11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    Range("T2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J10").Select
    ActiveSheet.Paste
    Windows("orchard market fruitport.xls").Activate
    Range("S2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J11").Select
    ActiveSheet.Paste
    Windows("orchard market fruitport.xls").Activate
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    Range("K2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("B13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("L2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("B14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("M2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("B15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("N2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("B16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("O2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("D16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("P2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("G16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    Range("U2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("V2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("W2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("X2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("Y2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("Z2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("K17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("AA2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("M17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Columns("AB:AB").ColumnWidth = 7
    Range("AC2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "="
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = "= IF(1,""COD"",""CHARGE"")"
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("B2:B17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    ActiveWindow.SmallScroll Down:=12
    Range("B22:B44").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("F2:F17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("D22:D37").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("G2:G17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("G22:G38").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("H2:H17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("M22:M37").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("I2:I17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("K22:K37").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("D2:D17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("H22:H37").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("orchard market fruitport.xls").Activate
    Range("E2:E17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
    Range("J22:J37").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-24
    Windows("orchard market fruitport.xls").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    Windows("LINEN SERVICE AGREEMENT Template.xls").Activate
End Sub
 
Sort by date Sort by votes
Hi Caleeco,
Below is what I managed to make work. It is based off of the original Macro created by the macro recorder and your macro. I know it is cumbersome but it accomplishes my end goal. I'm going to hold off writing the rest of this macro using this method for the weekend to see if you or someone else can discover why the value-paste method is not working. After that I'd just like to kick this thing in the A$$ so I dont need to worry about it anymore.
Code: 
Sub WorkbookPrompt()


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


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


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


    wb2.Activate
    Range("G8").Select
    Selection.Copy
    wb1.Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub
Thanks again for all you have done thus far to tackle my problem!
Joe
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
181
jolivanes
jolivanes
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
189
kbk
K
G
  • Solved
How do I automatically insert into next blank row..
Replies
2
Views
649
ghrek
G
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
420
myall_blues
myall_blues

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