visual basic command to special paste a row of formulas into value

I am currently using this macro to copy a row of values from one sheet to
another using vsual basic editor

strFrom = "[Data Source - Football]Source Prior Year Statistics!"
strTo = "[Data Source - Football]Source Prior Year +1 Statistics!"

CopyRangeAdd enumRange, strFrom & "J16:U21", strTo & "J16:U21"

Currently this macro is copying values in one sheet to a value in another
sheet

I would now like to copy formulas in one sheet to values in another sheet

well, the code that does the copying is hidden in the unrevealed
CopyRangeAdd. If you want that modified, you would need to provide it.

else do

Range(strFrom & "J16:U21").copy Range(strTo & "J16:U21")

--
Regards,
Tom Ogilvy


"F.C" <[email protected]> wrote in message
news:[email protected]...
> I am currently using this macro to copy a row of values from one sheet to
> another using vsual basic editor
>
> strFrom = "[Data Source - Football]Source Prior Year Statistics!"
> strTo = "[Data Source - Football]Source Prior Year +1 Statistics!"
>
> CopyRangeAdd enumRange, strFrom & "J16:U21", strTo & "J16:U21"
>
> Currently this macro is copying values in one sheet to a value in another
> sheet
>
> I would now like to copy formulas in one sheet to values in another sheet
>
>
>

Dear Tom

Thank you for spending the time to solve my problem.
The programmer who wrote this is no longer doing this work
and therefore I have to find the answers to any problems that arise

Here is the full macro

Private Sub CopyRangeAdd(ByVal eType As typCopyRangeEnum, ByVal strFrom As
String, Optional ByVal strTo As String = "")

gintCopyRangesCount = gintCopyRangesCount + 1
ReDim Preserve aCopyRanges(1 To gintCopyRangesCount) As typCopyRange

With aCopyRanges(gintCopyRangesCount)
.strFrom = strFrom
.strTo = strTo
.eType = eType
End With
End Sub


Public Sub CopyRangesInitialise()
Dim strFrom As String, strTo As String

'Reset the copy ranges
gintCopyRangesCount = 0
Erase aCopyRanges

'Set up the copy ranges
'Syntax:
' CopyRangeAdd "From" [,"To"]
'
' If "To" is specified, the range will be copied
' If "To" is not specified or blank (""), the "From" range will be erased
'
'Format is: "[<Workbook>]<Sheet>!<RangeStart>:<RangeEnd>"

strFrom = "[Data Source - Golf]Source Current Year Statistics!"
strTo = "[Data Source - Golf]Source Budget Year Statistics!"

CopyRangeAdd enumRange, strFrom & "J14:U14", strTo & "J14:U14"
CopyRangeAdd enumRange, strFrom & "J20:U20", strTo & "J20:U20"
CopyRangeAdd enumRange, strFrom & "J24:U24", strTo & "J24:U24"

SO INSTEAD OF COPYING VALUES FROM ONE SHEET TO ANOTHER SHEET I WANT TO COPY
A RANGE THAT CONTAINS "FORMULAS" IN ONE SHEET TO "VALUES" IN ANOTHER SHEET.

Tom can you assit?












"Tom Ogilvy" wrote:

> well, the code that does the copying is hidden in the unrevealed
> CopyRangeAdd. If you want that modified, you would need to provide it.
>
> else do
>
> Range(strFrom & "J16:U21").copy Range(strTo & "J16:U21")
>
> --
> Regards,
> Tom Ogilvy
>
>
> "F.C" <[email protected]> wrote in message
> news:[email protected]...
> > I am currently using this macro to copy a row of values from one sheet to
> > another using vsual basic editor
> >
> > strFrom = "[Data Source - Football]Source Prior Year Statistics!"
> > strTo = "[Data Source - Football]Source Prior Year +1 Statistics!"
> >
> > CopyRangeAdd enumRange, strFrom & "J16:U21", strTo & "J16:U21"
> >
> > Currently this macro is copying values in one sheet to a value in another
> > sheet
> >
> > I would now like to copy formulas in one sheet to values in another sheet
> >
> >
> >
>
>
>

We still haven't gotten to the code that does the actual copy.

This looks like it might be some type of class module that was set up. The
documentation for the copyrangeAdd function hasn't been updated - it shows 2
arguments and the function has 3 argument. the etype argument may have an
option to tell it how to copy (ex, copy formulas to values), but that isn't
documented. The code you show appears to be building an array of defined
types that contain an 'etype', 'from' and 'to' entries. I assume this will
eventually be utilized by whatever code actually does the copying. So far,
you haven't shown that code.

--
Regards,
Tom Ogilvy

"F.C" <[email protected]> wrote in message
news:[email protected]...
> Dear Tom
>
> Thank you for spending the time to solve my problem.
> The programmer who wrote this is no longer doing this work
> and therefore I have to find the answers to any problems that arise
>
> Here is the full macro
>
> Private Sub CopyRangeAdd(ByVal eType As typCopyRangeEnum, ByVal strFrom As
> String, Optional ByVal strTo As String = "")
>
> gintCopyRangesCount = gintCopyRangesCount + 1
> ReDim Preserve aCopyRanges(1 To gintCopyRangesCount) As typCopyRange
>
> With aCopyRanges(gintCopyRangesCount)
> .strFrom = strFrom
> .strTo = strTo
> .eType = eType
> End With
> End Sub
>
>
> Public Sub CopyRangesInitialise()
> Dim strFrom As String, strTo As String
>
> 'Reset the copy ranges
> gintCopyRangesCount = 0
> Erase aCopyRanges
>
> 'Set up the copy ranges
> 'Syntax:
> ' CopyRangeAdd "From" [,"To"]
> '
> ' If "To" is specified, the range will be copied
> ' If "To" is not specified or blank (""), the "From" range will be
erased
> '
> 'Format is: "[<Workbook>]<Sheet>!<RangeStart>:<RangeEnd>"
>
> strFrom = "[Data Source - Golf]Source Current Year Statistics!"
> strTo = "[Data Source - Golf]Source Budget Year Statistics!"
>
> CopyRangeAdd enumRange, strFrom & "J14:U14", strTo & "J14:U14"
> CopyRangeAdd enumRange, strFrom & "J20:U20", strTo & "J20:U20"
> CopyRangeAdd enumRange, strFrom & "J24:U24", strTo & "J24:U24"
>
> SO INSTEAD OF COPYING VALUES FROM ONE SHEET TO ANOTHER SHEET I WANT TO
COPY
> A RANGE THAT CONTAINS "FORMULAS" IN ONE SHEET TO "VALUES" IN ANOTHER
SHEET.
>
> Tom can you assit?
>
>
>
>
>
>
>
>
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > well, the code that does the copying is hidden in the unrevealed
> > CopyRangeAdd. If you want that modified, you would need to provide it.
> >
> > else do
> >
> > Range(strFrom & "J16:U21").copy Range(strTo & "J16:U21")
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "F.C" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am currently using this macro to copy a row of values from one sheet
to
> > > another using vsual basic editor
> > >
> > > strFrom = "[Data Source - Football]Source Prior Year Statistics!"
> > > strTo = "[Data Source - Football]Source Prior Year +1
Statistics!"
> > >
> > > CopyRangeAdd enumRange, strFrom & "J16:U21", strTo & "J16:U21"
> > >
> > > Currently this macro is copying values in one sheet to a value in
another
> > > sheet
> > >
> > > I would now like to copy formulas in one sheet to values in another
sheet
> > >
> > >
> > >
> >
> >
> >