First, since you recorded the code and it relies on the activecell and
activesheet, it really makes it difficult to see what's happening.

But maybe this will help.

dim ToWks as worksheet
dim FromWks as worksheet

dim ShtName as string

dim RngToCopy as range
Dim DestCell as range

set fromwks = workbooks("qc-100%.xls").worksheets("somesheetnamehere")
shtname = cstr(fromwks.range("A1").value)

set towks = nothing
on error resume next
set towks = workbooks(shtname & ".xls").worksheets(shtname)
on error goto 0

if towks is nothing then
msgbox "either no workbook with that name--or worksheet with that name"
exit sub
end if


with fromwks
set rngtocopy = .range("a1:A10") 'I couldn't tell what you were copying
end with

with towks
set destcell = .range("x1") 'I couldn't tell where you were pasting
end with

rngtocopy.copy _
destination:=destcell

=========

Untested, uncompiled. Watch for typos.

Kashyap wrote:

Hi Dave,

I have code something like below..

Windows("QC-100%.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Regina.xls").Activate
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
Windows("QC-100%.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Regina.xls").Activate
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
Windows("QC-100%.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Regina.xls").Activate
ActiveCell.Offset(0, 2).Range("A1").Select

.................. and continues..

So I need a code to replace "Windows("Regina.xls").Activate" where it
detects the name (Regina in this case) from A1. Also, sheet name will be same
as file name (Regina)

Thanks..

"Dave Peterson" wrote:

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


--

Dave Peterson