I m trying to save my excel file as a pdf. all is working great except the fact that i have a few cells that contain hyperlinks (via HYPERLINK formula). when exported, they are being converted to a simple text, loosing the link.
thus my question is this, would anyone know of a proper way to export an excel file so that ALL hyperlinks are preserved? be it with excel or any other program/converter.
I have a simlple macro to add a new customer to a list using a form. One of the columns of the datra beig added is an email address. When entering the data the email address just appears as text, but i want it to be the email address link you could click on and it opens up an outlook email. Just clicking into the cell and tabbing out changes this into the format i want, but wondering how to i get the macro to do this?
View 3 Replies View RelatedI am trying to store values into a dynamic array. The size of the array will vary each time, so I need the range to be dynamic. Most importantly, I need all values to be retained in the array. Currently, a value will be stored, but once the next round of the for loop is initiated, the array changes to "<subscript out of range>" and stays that way until the it is replaced by the next value. So, there isn't an accumualtion in the array--it goes one value, to out of range, to one value, etc.
View 8 Replies View RelatedI am having when trying to preserve a hyperlink when using VLOOKUP. I have two worksheets within the same workbook. Worksheet 1 contains a lot of information for internal use(many of the cells in all of the columns contain hyperlinks to web addresses), and Worksheet 2 should be a version identical to this, showing only the selected columns suitable for external use. This is to avoid using two different 'work trackers'; so, when info in Worksheet 1 is updated, Worksheet 2 should automatically be updated and reflect this.
I am currently using the following code:
Function GetHyperLink(r As Range) As String
If r.Hyperlinks.Count Then
GetHyperLink = r.Hyperlinks(1).Address
End If
[Code].....
I have made a pivot table and in Pivot table option Merge labels for colum b. I the problem i am facing is once i give this the next colum text filed is automatically arranged in center. I want the next colum as left alignment. Once i delete some rows in the data then if i refresh it automatically align the next colum to center. I have given the preserve formatting as well as i have un selected the autoformat colum.
View 2 Replies View RelatedI have programmed a button that allows the user to import XML data into my workbook via the following
Sub Button5_Click()
FileToOpen = Application. GetOpenFilename("XML files (*.xml), *.xml")
If FileToOpen <> False Then
ActiveWorkbook.XmlMaps("RouteDoc_Map").Import (FileToOpen)
End If
End Sub
All my XML data comes in great to the associated mapped cells (on several worksheets) but the data that is coming in as a list erases the alternating white/gray conditional formating that I've applied via FORMULA IS =MOD(ROW(),2). How would I be able to preserve the alternating white/gray conditional formating, no matter how many rows are being imported via my button ?
I'm using IF functions to get the difference of two cells. An example:
View 5 Replies View RelatedI have an inventory sheet with rows containing a formula. I've placed the same formula in all 65536 rows that Excel 2003 has.
I have a macro to delete unused items. It searches for an item number then deletes that row. (Actually it deletes the item number and a partial row based on an offset, but that was for an earlier version and an entire row would be okay) When a row is deleted, all the other rows move up and Excel creates a new last row (65536) The problem is that this new row has no formulas. While it's probably unlikely that I'll ever blow through 65536 rows, it seems sloppy to leave this unaccounted for.
Is there a way to either add the existing formula(s) to the last row...or insert a new row *somewhere* that is empty except for the formula of the other rows in the sheet? Here's what I have for the existing code.
Code:
'Search for item to delete based on entry to InputBox
Sub Delete_Item()
Dim FindString As String
Dim Rng As Range
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[code].....
Within Worksheet_BeforeDoubleClick, I have the following simple code extract that sets the appropriate autofilter to the correct value (and highlights a couple of cells for clarity) when a particular cell is double-clicked...
If Not Intersect(Target, Range("C10:AQ11")) Is Nothing Then
If Not Intersect(Target, Range("C10:D11")) Is Nothing Then
Selection.AutoFilter Field:=10, Criteria1:="C"
Range("C10:D11").Interior.ColorIndex = 36
End If
You'll be happy to hear that the event works as expected.
However I would like to add the following enhancement: after Worksheet_BeforeDoubleClick completes the "double-clicked cell" is selected, is it possible for the "original" cell to be re-selected once the event completes?
My current headache is that I want to be able to reinstate excel formula when the delete key is hit. However there this is a slight catch. I don't just want to be able to do this for one cell but reinstate the same excel forumla in an entire cloumn (ie for multiple rows). On top of this I also need to find a way of being able to do this for multiple columns (ie column 1 has formula 1, column 2 has formula 2 etc.....)
I have found this but I am having trouble adapting it to suit my purpose. This code just reinstates the formula for 1 cell.
Worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address "$A$1" Then
Application.OnKey "{del}"
Else
Application.OnKey "{del}", "myFormula"
End If
End Sub
Sub myFormula()
Worksheets("Sheet1").Range("A1").Formula = "=RC[1]+RC[2]"
End Sub
I am making template and trying to preserve formatting when users paste into a cell. From my research, I have found that this is impossible with simple format protection, it works to prevent changing formating, but if something is pasted (with normal paste), the format will change.
I have found a few solutions, but can't get them to work correctly
Solution A: Disable normal paste and substitute Paste Special - Values
How will this work with pasting via CTRL + V and the right click menu? I need both to function.
Solution B: Allow users to paste normally, but use a macro to copy formatting from a hidden sheet
I actually found code for this, but I couldn't figure out exactly how to impliment it. The sheet that users will be pasting into is called "Batch Summary" and I was trying to pull formatting from a sheet names "Batch Summary Format", but was getting an error.
I have noticed that if I create a row and put a reference formula in one of the cells (i.e. in Cell B3 I have "=B4", to show contents of B4), then after I do Sort and Row 3 moves else where, formula in what used to be B3 no longer points to B4, but rather to B<new row + 1>.
Absolute reference doesn't seem to be an option (i.e. "=$B$4") since after sorting rows in question the cell references in my B cells do get screwed up. I guess I want to bind a certain cell to a certain other cell, and keep that bind no matter where I move the row.
Is it possible to preserve original text formatting when copying and pasting only part of a cell. Like, for example, a cell may contain ten words one of which is in red and underlined. How do you copy that one word and paste it in another cell so that it remains in red and underlined?
View 4 Replies View RelatedHow would I modify a (very long) array formula, while preserving the brackets? Or re-creating them? I've tried doing it "at the cell" without luck.
View 2 Replies View Related have come across the apparent inflexibility of a pivot chart retaining its user defined formatting whenever it is refreshed.
I understand I can save my chosen formatting as a 'user defined' custom chart, which can be applied post-refresh, but was wondering if a simple bit of code would do this automatically, saving the additional clicks!
how to deal with the attached. How could I adapt the code to call in other 'user defined' charts I may create in the future?
I'm using the code below to do a simple search & replace. The code is working fine.
My problem is after the search & replace is done, certain cells that contain numbers look weird with decimal points & plus signs similar to Format Cells - Scientific. How do I prevent these cells from looking 'weird' in my code?
I've attached a workbook with 2 sheets called Before & After. You can see the 'weird' cells in After.
Sub Macro1()
Cells.Replace What:=": ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
With ADO (ADODB.Connection), is there a way to preserve the cell formatting on the worksheet that the recordset data is copied to? Right now, if I format a Cell's font and font-size, then run the Macro to refresh the data, the formatting is gone and it's back to default formatting.
View 2 Replies View RelatedI'm writting an Excell VB application which reads data from my spreadsheet and places it into the text member of an IXMLDOMElement node in order to generate a QBXML request.
The cell I am reading from has datatype number with two decimal places as required by the xml scheme. The problem is that the Range.Value Variant data type drops the decimal point and any trailing zeros so that 1197.00 become 1197. Unfortunately, the QBXML parser requires to see the decimal as well as trailing zeros. So, 1197.90 must show up as precisely that. Changing 1197.90 into 1197.9, for example, also breaks the xml.
My Excel VBA book specifically states that a Variant data type holding numerical data will maintain the data type assigned to it.
I also tried declaring a local decimal (Dim MyDecimal As Decimal) thinking to try copying the Range.Value value to it before assigning to the QBXML field in order to force the correct data format but my VBA macro compiler does not apparently recognize "Decimal" as a valid data type event though my book clearly indicates it is one of the valid VB data types.
I am using Excel 2000. Are these problems solved in newer versions?
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
I will be receiving a regular .csv file with data I need to manipulate in Excel. There will be a column of data, some of which has leading zeros. These are critical text data - in Excel it's easy enough to format a cell as text - but when I OPEN a new CSV, it assumes numbers and doesn't save them. When I change the format to text, it does not re-populate that column correctly - I'm going to have to catch it on the "open in Excel" command .
View 4 Replies View RelatedI have 2 columns which contain numbers that have laeding zeros. example:
Column H has 002345 and Column I has 0678. I have tried H1&I1 but the leading zeros are stripped off. Is there a way to combine 2 cells AND preserve the leading zeros in both cells ?
I have a cell with some text in it that has formatting applied (bold in this case). I want to append some text on the end using VBA, but preserve the existing font formatting information. So say I have "one bold one" in my cell, I'm executing the following: ActiveCell.Value = ActiveCell.Value & "two"
But this wipes out the bold information. Is there any way of preserving the bold that's already in the cell. Either perhaps by copying the Characters object and reapplying it, or by some other method?
I'm having a devil of a time trying to preserve the formatting on a pivot table that I made in Excel 2003. As near as I can tell, I've done the appropriate things --- check the preserve formatting box, uncheck autoformat --- but my formatting is not preserved when I pivot.
Adding to the mystery is the fact that some formatting is preserved but not all. I'm guessing either I'm missing something obvious or this is just an unruly, rogue pivot table that refuses to be formatted.
I am brand new and I don't know a ton about macros and programming in Excel.
What I'm trying to do here in this attached spreadsheet is at the end of inputting my line, I want to hit the tab (marked in red to make it clear where) button in the cell and have it automatically create another blank line but keep the formulas the same.
Basically I just want to avoid having to right click and insert line and then drag the formulas column by column down which is difficult.
See attached : TEST2_2014 Sales & Commission Spreadsheet.xlsx
I have a macro that enables me to split my master table into separate tabs based on the first column. However, the column widths and the header format is not retained. Is there something I can add to the macro below to keep the formatting?
Code:
Sub DispatchTimeSeriesToSheets() Dim ws As Worksheet
Set ws = Sheets("MasterList")
Dim LastRow As Long
LastRow = Range("A" & ws.Rows.Count).End(xlUp).Row
[Code] ......
I found quite a few posts about this problem, but none of the answers was any use to me. I need to redimension a 2 dimensional array in a Sub. I deleted all the code that is not of interest:
View 3 Replies View RelatedI have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.
I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if
The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.
Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"
[Code] .......
If I run this vba it removes the external data connection to the pivot table:
ActiveWorkbook.Connections("EXAMPLE").Delete
When I attempt to drill down into the pivot table it states the data connection was removed.
Is there a way to remove the data connection but preserve the data in the pivot cache?
It's probably possible to store the data somewhere else in the document and link the pivot table up to it but that seems cumbersome.
I have created a DDE link which is connected to Excel and refreshes on every change but the problem is it's only 1 row in height, so as new data comes in I lose the previous. Instead I want the old data to move down a row and I want this to continue as new data comes in.
View 9 Replies View RelatedI have a couple of workbooks, workbook1 pulls information from closed workbook2. The current link works fine, it returns the value that is in cell E10 from the worksheet 'Totals' from within the workbook 'Week 12 Yellow.xlsm'.
[Code]....
What I would like to do is make two parts of this link variable; Name (so 'Marc owens' in the above example) Worksheet (so 'Week 12 Yellow.xlsm in the above example)
As stated, the worksheet in the formula will be closed so I cannot use the 'Indirect' option. I have come across a lot of talk about the 'Concatenate' option but this this isn't working, don't know if its the way I have the formula or if I need something extra adding.
Cell A1 = name variable & Cell A2 = worksheet variable
[Code] ......
But this just returns "G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals!%E%10" in the cell the formula is in.
I've changed it so that it is
[Code] ......
But again I get "='G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals'!%E%10" returned which is the same formula as originally used (top of page) but it doesn't return a value.
