+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    F.C
    Guest

    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




  2. #2
    Tom Ogilvy
    Guest

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

    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" <FC@discussions.microsoft.com> wrote in message
    news:9DDB9661-7696-404D-9A83-7156F050A6DB@microsoft.com...
    > 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
    >
    >
    >




  3. #3
    F.C
    Guest

    Re: visual basic command to special paste a row of formulas into v

    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" <FC@discussions.microsoft.com> wrote in message
    > news:9DDB9661-7696-404D-9A83-7156F050A6DB@microsoft.com...
    > > 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
    > >
    > >
    > >

    >
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: visual basic command to special paste a row of formulas into v

    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" <FC@discussions.microsoft.com> wrote in message
    news:C9CA740F-FD62-4BB3-BA74-C99363DDBEE7@microsoft.com...
    > 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" <FC@discussions.microsoft.com> wrote in message
    > > news:9DDB9661-7696-404D-9A83-7156F050A6DB@microsoft.com...
    > > > 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
    > > >
    > > >
    > > >

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1