Macro to paste value to a file same as A1
Maybe...
This kind of code would go in the master.xls project:
Option Explicit
Sub testme()
Dim wkbk as workbook
dim RngToCopy as range
dim DestCell as range
If activeworkbook.name = thisworkbook.name then
'you're in the right master
else
msgbox "activate a sheet in master.xls and try again!"
exit sub
end if
set wkbk = nothing
on error resume next
set wkbk = workbooks(activesheet.range("A1").value & ".xls")
on error goto 0
if wkbk is nothing then
msgbox "No workbook named: " & activesheet.range("A1").value & " is open!"
exit sub
end if
set rngtocopy = activesheet.range("a1:b99")
set destcell = wkbk.worksheets(1).range("x1")
rngtcopy.copy _
destination:=destcell
end if
(Untested, uncompiled. Watch for typos.)
And I just copied a range from that sheet to the left most worksheet in the
workbook with the name in A1.
Kashyap wrote:
I have a file (Master.xls) with several sheets with different names. A1 in
each sheet is same of Sheet name (ABC, MNO etc) and I also have different
files as per each sheet (ABC.xls, MNO.xls etc) which will be open.
Right now I have separate macro for each sheet as I need to update some
values from master.xls to ABC.xls or MNO.xls etc as per sheet names.
can I have a single macro so that it will update the values from master.xls
to ABC.xls or MNO.xls etc according to name in A1 in each sheet.
Also, I'll not be updating values from all the tabs in master.xls at once.
May be from only one sheet at a time..
--
Dave Peterson
|