Hi Dave,

I have a file 'QC-100%.xls' with several sheets..

Names of the sheets and A1 of that particular sheets are same.. Say Regina

I have opened Regina's sheet and another file Regina.xls (file name as per
A1)..

Say I have 20 number from B4 to B24 which is in sheet Regina of QC-100%.xls
and has to be pasted on to Regina.xls in a particular order..

That is B4 should be pasted on to E7, B5 to G7, B6 to H7 and so on...

Similarly, I have Text from C4 to C24 and D4 to D24 which is in sheet Regina
of QC-100%.xls and has to be pasted on to Regina.xls in a particular order..

C4 to C24 may be to D9, F9, h9...
D4 to D24 to D12, F12, H12 and so on..

How could I do this?




"Dave Peterson" wrote:

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