Excel/copy link macro

Advertisement


Question
QUESTION: Hi Tom

I have a series of workbooks. One of the workbooks will feed information to the others.  The feeder workbook is called “feeder”.

The workbooks that are fed the information are:

RE Smart
PE Smart
MU Smart
HIS Smart
GEO Smart

When the macro is run I would like “D2:AI2” from the “feeder” workbook to be copied to “AJ2:BO2” in the following workbooks:

RE Smart
PE Smart
MU Smart
HIS Smart
GEO Smart

All of the workbooks will be located in the folder C:\merge

Is this possible?


Thanks in advance.


Chris Mitchell

ANSWER: Christopher Mitchell,

You made no mention of what sheet, so I assumed a sheet named Sheet1 in each case.

I assume feeder is actually feeder.xlsm.  I assume it will contain the macro to be run and will be open.  

Assume no merged cells involved.


Sub ABCD()
Dim v as Variant, bk1 as Workbook, sh as worksheet
Dim bk as workbook, i as long

v = Array("RE Smart.xlsx", "PE Smart.xlsx", "MU Smart.xlsx", "HIS Smart.xlsx", _
   "GEO Smart.xlsx")
set bk1 = Workbooks("Feeder.xlsm")
set sh = bk1.Worksheets("Sheet1")
for i = lbound(v) to Ubound(v)
 Workbooks.Open "C:\merge\" & v(i)
 set bk = Activeworkbook
 sh.Range("D2:AI2").copy bk.Worksheets("Sheet1").Range("AJ2")
 bk.close SaveChanges:=True
Next
End Sub


If you just want to copy the values from feeder (no formulas) then
sh.Range("D2:AI2").copy bk.Worksheets("Sheet1").Range("AJ2")

becomes

sh.Range("D2:AI2").copy
bk.Worksheets("Sheet1").Range("AJ2").Pastespecial paste:=xlValues


Code is untested but should point the way. Adjust the code to reflect you real situation vice my assumptions.

--
Regards,
Tom Ogilvy





---------- FOLLOW-UP ----------

QUESTION: Hi Tom

This macro seems to work but only after several debug alerts.  Is there any way of simply bypassing these errors, using a line in the code?

Chris Mitchell

Answer
Chris Mitchell,

generally a debug alert means there is a problem.  you can do

Sub ABCD()
Dim v as Variant, bk1 as Workbook, sh as worksheet
Dim bk as workbook, i as long
On error resume next
v = Array("RE Smart.xlsx", "PE Smart.xlsx", "MU Smart.xlsx", "HIS Smart.xlsx", _
   "GEO Smart.xlsx")
set bk1 = Workbooks("Feeder.xlsm")
set sh = bk1.Worksheets("Sheet1")
for i = lbound(v) to Ubound(v)
 Workbooks.Open "C:\merge\" & v(i)
 set bk = Activeworkbook
 sh.Range("D2:AI2").copy bk.Worksheets("Sheet1").Range("AJ2")
 bk.close SaveChanges:=True
Next
On error goto 0
End Sub

but then you don't know what the macro might be doing.  It is better to evaluate the debug message and determine what is causing it.  If you hit Debug, then the offending line of code should be highlighted in yellow.  You can hover over parts of it (variables) and see what values they hold and so forth.  Look at the line and see what may be problematic about it.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.