Running a Macro From a Different Workbook

A

avd88

Board Regular
Joined
Jan 18, 2016
Messages
112
Hello maybe someone can give me a hand.

In our office we have a spreadsheet called operations.xlsb which has several macros recorded on it, so whenever someone opens excel, this file opens and they are allowed to run those macros when they need them from different files.

I usually make my macros through activex, I am trying to add a module in the operations.xlsb workbook, this macro just copies certain columns of a specific spreadsheet and pastes them in a new workbook in a certain order.

This is so a person in another city can run it on a workbook (intercias) she updates every day, that way she can send me back the new file with the information that I need.

My code works; I have tried it before. I added the module to operations.xlsb and when I open the macros available on the developer tab I can see the new macro when I select Macros in: All Open Workbooks. Macro name: operations.XLSB!CS_Analysis

The problem is the macro is running in the operations.xlsb file instead of the intercias file, I can't change the code to reference the intercias workbook as the name of this file changes every day.

Any ideas of how I can get it to run in the file where I'm opening the macro in?

I am not allowed to modify the intercias file.

Here is my code:

Code: 
'Current Workbook variables'Set wbI = ThisWorkbook
Set wsI = wbI.ActiveSheet


'Output workbook variables
Set wbII = Workbooks.Add
wbII.ActiveSheet.Name = "Analysis"


With wbII


Set wsII = wbII.Sheets("Analysis")


'FIND LAST ROW '


copyLastrow = wsI.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row


pasteLastrowB = wsII.Range("B" & Rows.Count).End(xlUp).Row
pasteLastrowC = wsII.Range("C" & Rows.Count).End(xlUp).Row
pasteLastrowD = wsII.Range("D" & Rows.Count).End(xlUp).Row
pasteLastrowE = wsII.Range("E" & Rows.Count).End(xlUp).Row
pasteLastrowF = wsII.Range("F" & Rows.Count).End(xlUp).Row
pasteLastrowG = wsII.Range("G" & Rows.Count).End(xlUp).Row
pasteLastrowH = wsII.Range("H" & Rows.Count).End(xlUp).Row
pasteLastrowI = wsII.Range("I" & Rows.Count).End(xlUp).Row
pasteLastrowJ = wsII.Range("J" & Rows.Count).End(xlUp).Row
pasteLastrowK = wsII.Range("K" & Rows.Count).End(xlUp).Row
pasteLastrowL = wsII.Range("L" & Rows.Count).End(xlUp).Row
pasteLastrowM = wsII.Range("M" & Rows.Count).End(xlUp).Row
pasteLastrowN = wsII.Range("N" & Rows.Count).End(xlUp).Row
pasteLastrowO = wsII.Range("O" & Rows.Count).End(xlUp).Row
pasteLastrowP = wsII.Range("P" & Rows.Count).End(xlUp).Row
pasteLastrowQ = wsII.Range("Q" & Rows.Count).End(xlUp).Row
pasteLastrowR = wsII.Range("R" & Rows.Count).End(xlUp).Row
pasteLastrowS = wsII.Range("S" & Rows.Count).End(xlUp).Row
pasteLastrowT = wsII.Range("T" & Rows.Count).End(xlUp).Row
pasteLastrowU = wsII.Range("U" & Rows.Count).End(xlUp).Row
pasteLastrowV = wsII.Range("V" & Rows.Count).End(xlUp).Row
pasteLastrowW = wsII.Range("W" & Rows.Count).End(xlUp).Row
pasteLastrowX = wsII.Range("X" & Rows.Count).End(xlUp).Row
pasteLastrowY = wsII.Range("Y" & Rows.Count).End(xlUp).Row
pasteLastrowZ = wsII.Range("Z" & Rows.Count).End(xlUp).Row
pasteLastrowAA = wsII.Range("AA" & Rows.Count).End(xlUp).Row
pasteLastrowAB = wsII.Range("AB" & Rows.Count).End(xlUp).Row
pasteLastrowAC = wsII.Range("AC" & Rows.Count).End(xlUp).Row
pasteLastrowAD = wsII.Range("AD" & Rows.Count).End(xlUp).Row
pasteLastrowAE = wsII.Range("AE" & Rows.Count).End(xlUp).Row
pasteLastrowAF = wsII.Range("AF" & Rows.Count).End(xlUp).Row
pasteLastrowAG = wsII.Range("AG" & Rows.Count).End(xlUp).Row
pasteLastrowAH = wsII.Range("AH" & Rows.Count).End(xlUp).Row
pasteLastrowAI = wsII.Range("AI" & Rows.Count).End(xlUp).Row
pasteLastrowAJ = wsII.Range("AJ" & Rows.Count).End(xlUp).Row
pasteLastrowAK = wsII.Range("AK" & Rows.Count).End(xlUp).Row
pasteLastrowAL = wsII.Range("AL" & Rows.Count).End(xlUp).Row
pasteLastrowAM = wsII.Range("AM" & Rows.Count).End(xlUp).Row
pasteLastrowAN = wsII.Range("AN" & Rows.Count).End(xlUp).Row
pasteLastrowAO = wsII.Range("AO" & Rows.Count).End(xlUp).Row
pasteLastrowAP = wsII.Range("AP" & Rows.Count).End(xlUp).Row
pasteLastrowAQ = wsII.Range("AQ" & Rows.Count).End(xlUp).Row
pasteLastrowAR = wsII.Range("AR" & Rows.Count).End(xlUp).Row
pasteLastrowAS = wsII.Range("AS" & Rows.Count).End(xlUp).Row
pasteLastrowAT = wsII.Range("AT" & Rows.Count).End(xlUp).Row
pasteLastrowAU = wsII.Range("AU" & Rows.Count).End(xlUp).Row
pasteLastrowAV = wsII.Range("AV" & Rows.Count).End(xlUp).Row
pasteLastrowAW = wsII.Range("AW" & Rows.Count).End(xlUp).Row


'COPY PASTE'


wsI.Range("Q3:Q" & copyLastrow).Copy
wsII.Range("B" & pasteLastrowB).PasteSpecial (xlPasteValues)
wsI.Range("C3:C" & copyLastrow).Copy
wsII.Range("C" & pasteLastrowC).PasteSpecial (xlPasteValues)
wsI.Range("H3:H" & copyLastrow).Copy
wsII.Range("D" & pasteLastrowD).PasteSpecial (xlPasteValues)
wsI.Range("I3:I" & copyLastrow).Copy
wsII.Range("E" & pasteLastrowE).PasteSpecial (xlPasteValues)
wsI.Range("J3:J" & copyLastrow).Copy
wsII.Range("F" & pasteLastrowF).PasteSpecial (xlPasteValues)
wsI.Range("K3:K" & copyLastrow).Copy
wsII.Range("G" & pasteLastrowG).PasteSpecial (xlPasteValues)
wsI.Range("O3:O" & copyLastrow).Copy
wsII.Range("H" & pasteLastrowH).PasteSpecial (xlPasteValues)
wsI.Range("A3:A" & copyLastrow).Copy
wsII.Range("I" & pasteLastrowI).PasteSpecial (xlPasteValues)
wsI.Range("FT3:FT" & copyLastrow).Copy
wsII.Range("J" & pasteLastrowJ).PasteSpecial (xlPasteValues)
wsI.Range("FS3:FS" & copyLastrow).Copy
wsII.Range("K" & pasteLastrowK).PasteSpecial (xlPasteValues)
wsI.Range("NR3:NR" & copyLastrow).Copy
wsII.Range("L" & pasteLastrowL).PasteSpecial (xlPasteValues)
wsI.Range("NS3:NS" & copyLastrow).Copy
wsII.Range("M" & pasteLastrowM).PasteSpecial (xlPasteValues)
wsI.Range("D3:D" & copyLastrow).Copy
wsII.Range("N" & pasteLastrowN).PasteSpecial (xlPasteValues)
wsI.Range("L3:L" & copyLastrow).Copy
wsII.Range("O" & pasteLastrowO).PasteSpecial (xlPasteValues)
wsI.Range("M3:M" & copyLastrow).Copy
wsII.Range("P" & pasteLastrowP).PasteSpecial (xlPasteValues)
wsI.Range("N3:N" & copyLastrow).Copy
wsII.Range("Q" & pasteLastrowQ).PasteSpecial (xlPasteValues)
wsI.Range("EV3:EV" & copyLastrow).Copy
wsII.Range("R" & pasteLastrowR).PasteSpecial (xlPasteValues)
wsI.Range("EW3:EW" & copyLastrow).Copy
wsII.Range("S" & pasteLastrowS).PasteSpecial (xlPasteValues)
wsI.Range("EX3:EX" & copyLastrow).Copy
wsII.Range("T" & pasteLastrowT).PasteSpecial (xlPasteValues)
wsI.Range("FA3:FA" & copyLastrow).Copy
wsII.Range("U" & pasteLastrowU).PasteSpecial (xlPasteValues)
wsI.Range("ET3:ET" & copyLastrow).Copy
wsII.Range("V" & pasteLastrowV).PasteSpecial (xlPasteValues)
wsI.Range("EM3:EM" & copyLastrow).Copy
wsII.Range("W" & pasteLastrowW).PasteSpecial (xlPasteValues)
wsI.Range("EU3:EU" & copyLastrow).Copy
wsII.Range("X" & pasteLastrowX).PasteSpecial (xlPasteValues)
wsI.Range("EK3:EK" & copyLastrow).Copy
wsII.Range("Y" & pasteLastrowY).PasteSpecial (xlPasteValues)
wsI.Range("R3:R" & copyLastrow).Copy
wsII.Range("Z" & pasteLastrowZ).PasteSpecial (xlPasteValues)
wsI.Range("S3:S" & copyLastrow).Copy
wsII.Range("AA" & pasteLastrowAA).PasteSpecial (xlPasteValues)
wsI.Range("T3:T" & copyLastrow).Copy
wsII.Range("AB" & pasteLastrowAB).PasteSpecial (xlPasteValues)
wsI.Range("U3:U" & copyLastrow).Copy
wsII.Range("AC" & pasteLastrowAC).PasteSpecial (xlPasteValues)
wsI.Range("V3:V" & copyLastrow).Copy
wsII.Range("AD" & pasteLastrowAD).PasteSpecial (xlPasteValues)
wsI.Range("W3:W" & copyLastrow).Copy
wsII.Range("AE" & pasteLastrowAE).PasteSpecial (xlPasteValues)
wsI.Range("X3:X" & copyLastrow).Copy
wsII.Range("AF" & pasteLastrowAF).PasteSpecial (xlPasteValues)
wsI.Range("Y3:Y" & copyLastrow).Copy
wsII.Range("AG" & pasteLastrowAG).PasteSpecial (xlPasteValues)
wsI.Range("Z3:Z" & copyLastrow).Copy
wsII.Range("AH" & pasteLastrowAH).PasteSpecial (xlPasteValues)
wsI.Range("AA3:AA" & copyLastrow).Copy
wsII.Range("AI" & pasteLastrowAI).PasteSpecial (xlPasteValues)
wsI.Range("AB3:AB" & copyLastrow).Copy
wsII.Range("AJ" & pasteLastrowAJ).PasteSpecial (xlPasteValues)
wsI.Range("AC3:AC" & copyLastrow).Copy
wsII.Range("AK" & pasteLastrowAK).PasteSpecial (xlPasteValues)
wsI.Range("AP3:AP" & copyLastrow).Copy
wsII.Range("AL" & pasteLastrowAL).PasteSpecial (xlPasteValues)
wsI.Range("AQ3:AQ" & copyLastrow).Copy
wsII.Range("AM" & pasteLastrowAM).PasteSpecial (xlPasteValues)
wsI.Range("AR3:AR" & copyLastrow).Copy
wsII.Range("AN" & pasteLastrowAN).PasteSpecial (xlPasteValues)
wsI.Range("AS3:AS" & copyLastrow).Copy
wsII.Range("AO" & pasteLastrowAO).PasteSpecial (xlPasteValues)
wsI.Range("AT3:AT" & copyLastrow).Copy
wsII.Range("AP" & pasteLastrowAP).PasteSpecial (xlPasteValues)
wsI.Range("AU3:AU" & copyLastrow).Copy
wsII.Range("AQ" & pasteLastrowAQ).PasteSpecial (xlPasteValues)
wsI.Range("AV3:AV" & copyLastrow).Copy
wsII.Range("AR" & pasteLastrowAR).PasteSpecial (xlPasteValues)
wsI.Range("AW3:AW" & copyLastrow).Copy
wsII.Range("AS" & pasteLastrowAS).PasteSpecial (xlPasteValues)
wsI.Range("AX3:AX" & copyLastrow).Copy
wsII.Range("AT" & pasteLastrowAT).PasteSpecial (xlPasteValues)
wsI.Range("AY3:AY" & copyLastrow).Copy
wsII.Range("AU" & pasteLastrowAU).PasteSpecial (xlPasteValues)
wsI.Range("AZ3:AZ" & copyLastrow).Copy
wsII.Range("AV" & pasteLastrowAV).PasteSpecial (xlPasteValues)
wsI.Range("BA3:BA" & copyLastrow).Copy
wsII.Range("AW" & pasteLastrowAW).PasteSpecial (xlPasteValues)


End With




End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
If the posted code is located in the operations.XLSB workbook and you are using ThisWorkbook then the code will run against that workbook as ThisWorkbook refers to the workbook the code is in.

You could try using ActiveWorkbook instead of ThisWorkbook or you could alter the sub in operations.XLSB to take as an argument the workbook you want to run the code on.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
336
NdNoviceHlp
NdNoviceHlp
L
  • Solved
VBA Error
2 3
Replies
29
Views
1K
Joe4
Joe4
A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
1K
Joe4
Joe4
C
  • Question Question
How to automatically paste the data from one sheet to another using VBA
Replies
4
Views
278
Alex Blakenburg
A
O
  • Question Question
Creating workbooks for a team of staff
Replies
1
Views
207
HighAndWilder
H

Forum statistics

Threads
1,224,587
Messages
6,179,738
Members
452,940
Latest member
Lawrenceiow

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