help w/ generic copy & paste/paste special routine

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DavidH - 22 Jan 2006 00:03 GMT

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

Reply to this Message

Gary''s Student - 22 Jan 2006 00:20 GMT

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

Signature

Gary's Student

> I’m a macro novice who needs help with table-driven copy & paste macro.
>
[quoted text clipped - 17 lines]
>
> David

Reply to this Message

Dave Peterson - 22 Jan 2006 01:16 GMT

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.

> 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
[quoted text clipped - 43 lines]
> >
> > David

Signature

Dave Peterson

Reply to this Message

DavidH - 22 Jan 2006 18:30 GMT

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.

> You can actually use application.inputbox to get a range.
>
[quoted text clipped - 77 lines]
> > >
> > > David

Reply to this Message

Dave Peterson - 22 Jan 2006 19:44 GMT

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???

> 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:
[quoted text clipped - 95 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

DavidH - 23 Jan 2006 03:58 GMT

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.

> This might get you started, but there's lots of things that pastespecial can
> mean (values, formulas, formats???) and same with Appendbelow.
[quoted text clipped - 171 lines]
> > >
> > > Dave Peterson

Reply to this Message