Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help w/ generic copy & paste/paste special routine
Im a macro novice who needs help with table-driven copy & paste macro.
I have several worksheets that have macros that copy a source range to a destination range. Both the source range and the destination rage are named, but may be on different worksheets within the same workbook. Thanks to my companys adoption of Sarbanes-Oxley, any worksheets that contain macros are always suspect and subject to a higher level of testing. To alleviate the problem, Id like to design a generic copy routine that can be run from an add-in or personal.xls. I envision having a table in the target worksheet with these fields: source_range, destination_range, Paste_Values (e.g. an indicator to tell the macro whether to paste or past values), Append_Below (e.g. an indicator that instructs the macro to append the data to the bottom of the destination range). Im a macro novice and probably getting in over my head with this one. Does this approach seem workable? Do you have any suggestions for making the routine more flexible? Has anyone already done something like this? Thanks in advance, David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help w/ generic copy & paste/paste special routine
Here is a generic copy/paste posted in 2005 that polls the user for source
and destination. You could modify it to use fixed ranges or ranges specified in cells: Option Explicit Sub CopyFromPasteTo() Dim strF As String Dim strT As String Dim rngF As Range Dim rngT As Range strF = Application.InputBox("Enter from range", Type:=2) Set rngF = Range(strF) strT = Application.InputBox("Enter goto range", Type:=2) Set rngT = Range(strT) rngF.Copy rngT End Sub -- Gary's Student "DavidH" wrote: Im a macro novice who needs help with table-driven copy & paste macro. I have several worksheets that have macros that copy a source range to a destination range. Both the source range and the destination rage are named, but may be on different worksheets within the same workbook. Thanks to my companys adoption of Sarbanes-Oxley, any worksheets that contain macros are always suspect and subject to a higher level of testing. To alleviate the problem, Id like to design a generic copy routine that can be run from an add-in or personal.xls. I envision having a table in the target worksheet with these fields: source_range, destination_range, Paste_Values (e.g. an indicator to tell the macro whether to paste or past values), Append_Below (e.g. an indicator that instructs the macro to append the data to the bottom of the destination range). Im a macro novice and probably getting in over my head with this one. Does this approach seem workable? Do you have any suggestions for making the routine more flexible? Has anyone already done something like this? Thanks in advance, David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help w/ generic copy & paste/paste special routine
You can actually use application.inputbox to get a range.
Option Explicit Sub CopyFromPasteTo2() Dim rngF As Range Dim rngT As Range set rngf = nothing on error resume next set rngf = Application.InputBox("Enter from range", Type:=8) on error goto 0 if rngf is nothing then exit sub 'user hit cancel end if set rngt = nothing on error resume next set rngt = Application.InputBox("Enter goto range", Type:=8) on error goto 0 if rngT is nothing then exit sub 'user hit cancel end if rngF.areas(1).Copy _ destination:=rngT.cells(1) 'let excel resize the range. End Sub And excel will make sure that the user chose a range--you don't have to check for a valid address or what workbook/worksheet should be used. Gary''s Student wrote: Here is a generic copy/paste posted in 2005 that polls the user for source and destination. You could modify it to use fixed ranges or ranges specified in cells: Option Explicit Sub CopyFromPasteTo() Dim strF As String Dim strT As String Dim rngF As Range Dim rngT As Range strF = Application.InputBox("Enter from range", Type:=2) Set rngF = Range(strF) strT = Application.InputBox("Enter goto range", Type:=2) Set rngT = Range(strT) rngF.Copy rngT End Sub -- Gary's Student "DavidH" wrote: Im a macro novice who needs help with table-driven copy & paste macro. I have several worksheets that have macros that copy a source range to a destination range. Both the source range and the destination rage are named, but may be on different worksheets within the same workbook. Thanks to my companys adoption of Sarbanes-Oxley, any worksheets that contain macros are always suspect and subject to a higher level of testing. To alleviate the problem, Id like to design a generic copy routine that can be run from an add-in or personal.xls. I envision having a table in the target worksheet with these fields: source_range, destination_range, Paste_Values (e.g. an indicator to tell the macro whether to paste or past values), Append_Below (e.g. an indicator that instructs the macro to append the data to the bottom of the destination range). Im a macro novice and probably getting in over my head with this one. Does this approach seem workable? Do you have any suggestions for making the routine more flexible? Has anyone already done something like this? Thanks in advance, David -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help w/ generic copy & paste/paste special routine
what I had in mind as that the user would set up a sheet in his workbook
where he would enter the source and destination ranges into cells, like: Source Destination PasteSpecial? AppendBelow? SrcRng1 DestRng1 No No SrcRng2 DestRng2 No No SrcRng3 DestRng3 Yes No SrcRng4 DestRng4 Yes Yes SrcRng5 DestRng4 Yes Yes But I'm sure how to approach setting up a macro to do this. I assume that if I go this route, I would have to check for valid range names? Any thougths would be appreciated. "Dave Peterson" wrote: You can actually use application.inputbox to get a range. Option Explicit Sub CopyFromPasteTo2() Dim rngF As Range Dim rngT As Range set rngf = nothing on error resume next set rngf = Application.InputBox("Enter from range", Type:=8) on error goto 0 if rngf is nothing then exit sub 'user hit cancel end if set rngt = nothing on error resume next set rngt = Application.InputBox("Enter goto range", Type:=8) on error goto 0 if rngT is nothing then exit sub 'user hit cancel end if rngF.areas(1).Copy _ destination:=rngT.cells(1) 'let excel resize the range. End Sub And excel will make sure that the user chose a range--you don't have to check for a valid address or what workbook/worksheet should be used. Gary''s Student wrote: Here is a generic copy/paste posted in 2005 that polls the user for source and destination. You could modify it to use fixed ranges or ranges specified in cells: Option Explicit Sub CopyFromPasteTo() Dim strF As String Dim strT As String Dim rngF As Range Dim rngT As Range strF = Application.InputBox("Enter from range", Type:=2) Set rngF = Range(strF) strT = Application.InputBox("Enter goto range", Type:=2) Set rngT = Range(strT) rngF.Copy rngT End Sub -- Gary's Student "DavidH" wrote: I€„¢m a macro novice who needs help with table-driven copy & paste macro. I have several worksheets that have macros that copy a source range to a destination range. Both the source range and the destination rage are named, but may be on different worksheets within the same workbook. Thanks to my company€„¢s adoption of Sarbanes-Oxley, any worksheets that contain macros are always suspect and subject to a higher level of testing. To alleviate the problem, I€„¢d like to design a generic copy routine that can be run from an add-in or personal.xls. I envision having a table in the target worksheet with these fields: source_range, destination_range, Paste_Values (e.g. an indicator to tell the macro whether to paste or past values), Append_Below (e.g. an indicator that instructs the macro to append the data to the bottom of the destination range). I€„¢m a macro novice and probably getting in over my head with this one. Does this approach seem workable? Do you have any suggestions for making the routine more flexible? Has anyone already done something like this? Thanks in advance, David -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help w/ generic copy & paste/paste special routine
This might get you started, but there's lots of things that pastespecial can
mean (values, formulas, formats???) and same with Appendbelow. And with hardly any validation at all: Option Explicit Sub testme01() Dim KeyWks As Worksheet Dim testRngF As Range Dim testRngT As Range Dim myCell As Range Dim myRng As Range Dim myPasteSpecial As Boolean Dim myPasteBelow As Boolean Dim DestCell As Range Dim myMsg As String Set KeyWks = Worksheets("sheet1") With KeyWks Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells With myCell Set testRngF = Nothing Set testRngT = Nothing On Error Resume Next Set testRngF = Application.Range(.Value) Set testRngT = Application.Range(.Offset(0, 1).Value) On Error GoTo 0 myPasteSpecial = False If LCase(.Offset(0, 2).Value) = "yes" Then myPasteSpecial = True End If myPasteBelow = False If LCase(.Offset(0, 3).Value) = "yes" Then myPasteBelow = True End If If testRngF Is Nothing _ Or testRngT Is Nothing Then myMsg = "Invalid Range(s)" Else Set DestCell = testRngT.Cells(1) If myPasteBelow = True Then If IsEmpty(DestCell) Then 'keep it here ElseIf IsEmpty(DestCell.Offset(1, 0)) Then Set DestCell = DestCell.Offset(1, 0) Else Set DestCell = DestCell.End(xlDown).Offset(1, 0) End If End If If myPasteSpecial = True Then testRngF.Copy DestCell.PasteSpecial Paste:=xlPasteValues myMsg = "PasteSpecial" Else testRngF.Copy _ Destination:=DestCell myMsg = "just a paste" End If End If .Offset(0, 4).Value = myMsg End With Next myCell End Sub But there are lots of things that you have to test for. Make sure that SrcRng are single areas; maybe destrng's should be single cells??? DavidH wrote: what I had in mind as that the user would set up a sheet in his workbook where he would enter the source and destination ranges into cells, like: Source Destination PasteSpecial? AppendBelow? SrcRng1 DestRng1 No No SrcRng2 DestRng2 No No SrcRng3 DestRng3 Yes No SrcRng4 DestRng4 Yes Yes SrcRng5 DestRng4 Yes Yes But I'm sure how to approach setting up a macro to do this. I assume that if I go this route, I would have to check for valid range names? Any thougths would be appreciated. "Dave Peterson" wrote: You can actually use application.inputbox to get a range. Option Explicit Sub CopyFromPasteTo2() Dim rngF As Range Dim rngT As Range set rngf = nothing on error resume next set rngf = Application.InputBox("Enter from range", Type:=8) on error goto 0 if rngf is nothing then exit sub 'user hit cancel end if set rngt = nothing on error resume next set rngt = Application.InputBox("Enter goto range", Type:=8) on error goto 0 if rngT is nothing then exit sub 'user hit cancel end if rngF.areas(1).Copy _ destination:=rngT.cells(1) 'let excel resize the range. End Sub And excel will make sure that the user chose a range--you don't have to check for a valid address or what workbook/worksheet should be used. Gary''s Student wrote: Here is a generic copy/paste posted in 2005 that polls the user for source and destination. You could modify it to use fixed ranges or ranges specified in cells: Option Explicit Sub CopyFromPasteTo() Dim strF As String Dim strT As String Dim rngF As Range Dim rngT As Range strF = Application.InputBox("Enter from range", Type:=2) Set rngF = Range(strF) strT = Application.InputBox("Enter goto range", Type:=2) Set rngT = Range(strT) rngF.Copy rngT End Sub -- Gary's Student "DavidH" wrote: I€„¢m a macro novice who needs help with table-driven copy & paste macro. I have several worksheets that have macros that copy a source range to a destination range. Both the source range and the destination rage are named, but may be on different worksheets within the same workbook. Thanks to my company€„¢s adoption of Sarbanes-Oxley, any worksheets that contain macros are always suspect and subject to a higher level of testing. To alleviate the problem, I€„¢d like to design a generic copy routine that can be run from an add-in or personal.xls. I envision having a table in the target worksheet with these fields: source_range, destination_range, Paste_Values (e.g. an indicator to tell the macro whether to paste or past values), Append_Below (e.g. an indicator that instructs the macro to append the data to the bottom of the destination range). I€„¢m a macro novice and probably getting in over my head with this one. Does this approach seem workable? Do you have any suggestions for making the routine more flexible? Has anyone already done something like this? Thanks in advance, David -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help w/ generic copy & paste/paste special routine
Dave,
Thanks. This is beginning to look like more than I bargained for, but I'm going to give it a shot. Thanks again for getting me started. "Dave Peterson" wrote: This might get you started, but there's lots of things that pastespecial can mean (values, formulas, formats???) and same with Appendbelow. And with hardly any validation at all: Option Explicit Sub testme01() Dim KeyWks As Worksheet Dim testRngF As Range Dim testRngT As Range Dim myCell As Range Dim myRng As Range Dim myPasteSpecial As Boolean Dim myPasteBelow As Boolean Dim DestCell As Range Dim myMsg As String Set KeyWks = Worksheets("sheet1") With KeyWks Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In myRng.Cells With myCell Set testRngF = Nothing Set testRngT = Nothing On Error Resume Next Set testRngF = Application.Range(.Value) Set testRngT = Application.Range(.Offset(0, 1).Value) On Error GoTo 0 myPasteSpecial = False If LCase(.Offset(0, 2).Value) = "yes" Then myPasteSpecial = True End If myPasteBelow = False If LCase(.Offset(0, 3).Value) = "yes" Then myPasteBelow = True End If If testRngF Is Nothing _ Or testRngT Is Nothing Then myMsg = "Invalid Range(s)" Else Set DestCell = testRngT.Cells(1) If myPasteBelow = True Then If IsEmpty(DestCell) Then 'keep it here ElseIf IsEmpty(DestCell.Offset(1, 0)) Then Set DestCell = DestCell.Offset(1, 0) Else Set DestCell = DestCell.End(xlDown).Offset(1, 0) End If End If If myPasteSpecial = True Then testRngF.Copy DestCell.PasteSpecial Paste:=xlPasteValues myMsg = "PasteSpecial" Else testRngF.Copy _ Destination:=DestCell myMsg = "just a paste" End If End If .Offset(0, 4).Value = myMsg End With Next myCell End Sub But there are lots of things that you have to test for. Make sure that SrcRng are single areas; maybe destrng's should be single cells??? DavidH wrote: what I had in mind as that the user would set up a sheet in his workbook where he would enter the source and destination ranges into cells, like: Source Destination PasteSpecial? AppendBelow? SrcRng1 DestRng1 No No SrcRng2 DestRng2 No No SrcRng3 DestRng3 Yes No SrcRng4 DestRng4 Yes Yes SrcRng5 DestRng4 Yes Yes But I'm sure how to approach setting up a macro to do this. I assume that if I go this route, I would have to check for valid range names? Any thougths would be appreciated. "Dave Peterson" wrote: You can actually use application.inputbox to get a range. Option Explicit Sub CopyFromPasteTo2() Dim rngF As Range Dim rngT As Range set rngf = nothing on error resume next set rngf = Application.InputBox("Enter from range", Type:=8) on error goto 0 if rngf is nothing then exit sub 'user hit cancel end if set rngt = nothing on error resume next set rngt = Application.InputBox("Enter goto range", Type:=8) on error goto 0 if rngT is nothing then exit sub 'user hit cancel end if rngF.areas(1).Copy _ destination:=rngT.cells(1) 'let excel resize the range. End Sub And excel will make sure that the user chose a range--you don't have to check for a valid address or what workbook/worksheet should be used. Gary''s Student wrote: Here is a generic copy/paste posted in 2005 that polls the user for source and destination. You could modify it to use fixed ranges or ranges specified in cells: Option Explicit Sub CopyFromPasteTo() Dim strF As String Dim strT As String Dim rngF As Range Dim rngT As Range strF = Application.InputBox("Enter from range", Type:=2) Set rngF = Range(strF) strT = Application.InputBox("Enter goto range", Type:=2) Set rngT = Range(strT) rngF.Copy rngT End Sub -- Gary's Student "DavidH" wrote: I€„¢m a macro novice who needs help with table-driven copy & paste macro. I have several worksheets that have macros that copy a source range to a destination range. Both the source range and the destination rage are named, but may be on different worksheets within the same workbook. Thanks to my company€„¢s adoption of Sarbanes-Oxley, any worksheets that contain macros are always suspect and subject to a higher level of testing. To alleviate the problem, I€„¢d like to design a generic copy routine that can be run from an add-in or personal.xls. I envision having a table in the target worksheet with these fields: source_range, destination_range, Paste_Values (e.g. an indicator to tell the macro whether to paste or past values), Append_Below (e.g. an indicator that instructs the macro to append the data to the bottom of the destination range). I€„¢m a macro novice and probably getting in over my head with this one. Does this approach seem workable? Do you have any suggestions for making the routine more flexible? Has anyone already done something like this? Thanks in advance, David -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy; Paste; Paste Special are disabled | Excel Discussion (Misc queries) | |||
Copy/paste/paste special is not available | Excel Discussion (Misc queries) | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
VBA Code- Copy and paste into a generic text box | Excel Programming |