Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help me please understand this Visual Basic command | Excel Discussion (Misc queries) | |||
What is the Visual Basic command to select an entire row in Excel. | Excel Programming | |||
What is the Visual Basic command to select an entire row in Excel. | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
visual basic command in a cell?? | Excel Programming |