Macro to paste value to a file same as A1

[Kashyap]

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]

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]

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]

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]

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]

I'm not sure I understand.

QC-100%.xls contains a worksheet named Regina and in A1 of that sheet is the
value Regina.

So you opened that regina.xls workbook, but you didn't say what the name of the
worksheet that should get the pasted range. I'm gonna guess that it's the
leftmost (or only sheet in that workbook).

set fromwks = activesheet 'the sheet in QC-100%.xls
shtname = cstr(fromwks.range("A1").value)

set towks = nothing
on error resume next
set towks = workbooks(shtname & ".xls").worksheets(1)
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

fromwks.range("b4").copy _
destination:=towks.range("e7")

fromwks.range("b5").copy _
destination:=towks.range("g7")

and so on

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?

 

[Kashyap]

Hi Dave,

This is working fine, but I need to paste values only (without format /
formula)

 

[Dave Peterson]

Replace this:
fromwks.range("b4").copy _
destination:=towks.range("e7")

with:
towks.range("e7").value = fromwks.range("b4").value

It's not doing copy|paste special|values. It's just assigning the values.

Hi Dave,

This is working fine, but I need to paste values only (without format /
formula)

 

[Kashyap]

Hello Dave..

Below line is now giving any output..

towks.range("e7").value = fromwks.range("b4").value

I actually want copy|paste special|values from e7 to b4 and so on..

 

[Dave Peterson]

If E7 in the towks is empty after you run this macro, then B4 in the fromwks was
empty to start with.

Check your input cells.

And repeat that line as much as you need (etc!) and change the addresses to
match what you want.

Hello Dave..

Below line is now giving any output..

towks.range("e7").value = fromwks.range("b4").value

I actually want copy|paste special|values from e7 to b4 and so on..

 

[Kashyap]

Works exactly the way I want..

Thanks Dave..

If E7 in the towks is empty after you run this macro, then B4 in the fromwks was
empty to start with.

Check your input cells.

And repeat that line as much as you need (etc!) and change the addresses to
match what you want.