Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
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 Kashyap wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
Hi Dave,
This is working fine, but I need to paste values only (without format / formula) "Dave Peterson" wrote: 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 Kashyap wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
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. Kashyap wrote: Hi Dave, This is working fine, but I need to paste values only (without format / formula) "Dave Peterson" wrote: 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 Kashyap wrote: 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
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" wrote: 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. Kashyap wrote: Hi Dave, This is working fine, but I need to paste values only (without format / formula) "Dave Peterson" wrote: 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 Kashyap wrote: 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
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. Kashyap wrote: 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" wrote: 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. Kashyap wrote: Hi Dave, This is working fine, but I need to paste values only (without format / formula) "Dave Peterson" wrote: 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 Kashyap wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste value to a file same as A1
Works exactly the way I want..
Thanks Dave.. "Dave Peterson" wrote: 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. Kashyap wrote: 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" wrote: 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. Kashyap wrote: Hi Dave, This is working fine, but I need to paste values only (without format / formula) "Dave Peterson" wrote: 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 Kashyap wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
how to paste macro to a newly created file using vba | Excel Programming | |||
Cut and Paste Macro to new file | Excel Discussion (Misc queries) | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Select File Copy/Paste Macro Help.... | Excel Programming |