Posts by ChrisKelly1982

Re: Paste Special Workbook to Workbook via add-in through VBA

G'Day AEE,

No worries, will definately implement properly in future posts.

Thanks again,

Chris

Re: Paste Special Workbook to Workbook via add-in through VBA

G'Day Again Rory! ....Real-Time help...hahahah

Thats fantastic and all i needed to know. I can't see this problem being too much of a nuesence as majority of the time we are working with saved workbooks. But, i've definately learn't something so thanks very much for your help and hope to see you on here in the future.

Chris K.

Re: Paste Special Workbook to Workbook via add-in through VBA

G'Day Rory,

Thanks heaps for your quick reply.

I have tried paste special via the worksheet object but it doesn't seem to be the same paste special. Paste special from the Worksheet object will allow for pasting of my values as text or pasting as an object but does not expose the other xlPaste options such as xlPasteFormats or xlPasteColumnWidths.

My thoughts are still hovering in regards to the new un-saved instance of Excel. Even when trying to paste special manually (using right mouse click) to a new unsaved Instance of Excel i cannot expose the usual paste-special dialogue box. It is as if Excel cannot carry all the required formatting and column widths ETC on the clipboard accross instances of the Application object?

Kind Regards,

Chris

Re: Paste Special Workbook to Workbook via add-in through VBA

An Additional NOTE:

Have tested the above problem further and found that it only occurs when i try and paste special into a new, un-saved, workbook? It works fine when i open two existing workbooks and paste special from one to the other. My hunch is based purely on what i saw, which is that when i opened a new, un-saved excel doc it opens a fresh instance of Excel. However, when i open two existing workbooks, they seem to use the one instance of Excel? Not 100% sure if i am right though.

Hi OzGrid Users,

I am creating a series of generic functions for users distributed via the Excel add-in.
One simple series of functions includes paste special values, formats, and widths that are called from button controls on a custom toolbar (created when the add-in is installed by the user).

My code is currently hanging up on the paste special routines, particularly, it seems it cannot retreive what is on the clip board when pasting to a separate, open, workbook.

The code seems to work fine WITHIN a single workbook but cannot paste special when working between two open workbooks.

I have tried implementing paste special from the Worksheet and range object level and various permutations of setting the object variables i.e. Initialising from 'ThisWorkbook' or 'ActiveWorkbook'.

An example of one of the paste special routines is below:

Sub PasteVals()
Dim actWorkbook As Workbook
Dim actWorkSheet As Worksheet
Dim actRange As Range
Set actWorkbook = ActiveWorkbook
Set actWorkSheet = actWorkbook.ActiveSheet
Set actRange = actWorkSheet.Cells(ActiveCell.Row, ActiveCell.Column)
' Paste the vales in the selected location
 actRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         
End Sub

Your thoughts are most welcome!

Chris

Re: Updating Graphs with Dynamic Named Ranges Efficiently (Many Series)

Ahhh problem solved! Thanks a bunch for your help guys.

Accessed the SERIES formula for the graph on the formula bar and edited the Category range and data series range with my two named ranges.

Thanks very much again for your help,

Chris

Hello OzGrid!

First post....here we go!

I am looking for a way to set either the data source for a graph using a Single Dynamic Named Range OR easily create Dynamic Named Ranges for the multiple series (where talking about 119 series) contained within the chart. The technical side of my issue is described below.

• I have tried to adjust the 'Data Source Range, of the Data Source Tab, for a graph to look at a Single Dynamic Named Range (encompasses my whole data set). I found upon closing and re-opening the graph, the dynamic range data source is lost and replaced with the generic cell referencing. If this works it would be GREAT and save me creating 119 dynamic named ranges and having to updating them all in the graph series tab.

I can't currently upload an example of the data but can explain its basic structure.

• There is a series of 119 ground marks stored in one column (running down the page).
• Each ground mark has, stored against it in consecutive columns, the amount it has moved over time. There is an equal number of moves for every mark and about 30 moves in total for each mark and the data set is always being added to.

I can always resort to VBA but would love to hear if it can be done without code as my co-workers need to be able to understand process as well.

Thanks very much OzGrid Community!