formats

Hello, I have below piece of code in sub that copy a range and paste the selected in another range, it works fine but how can I make it to copy and paste the selected as values and formats I tried to use ".Paste" but I got an error Sheet2.Range("Sales_List_All").Copy...

Hi, I'm having some issues with date formats from a workbook downloaded from a Govt/County site. The date as downloaded shows as two different date formats inside one column: 03/17/2016 02:22:00 PM - formatted as General, and 2/09/16 13:29 - formatted as Custom (d/mm/yy h:mm) The issue is...

I like the ability to use conditions, such as [<100], or colours, such as [Red], in custom number formats in cells. But can I use these, or an equivalent, using Format in VBA? My current requirement is something like this: shtCalendar.Cells(rt, cPlace) = shtEvents.Cells(r, cEventPlace) &...

First off I'm newish to VBA (so please be kind) I trying to write a Sub that will loop through a number of cells (values and formats) from a "Source" worksheet to a "Target" worksheet. I found some code that copies values and formats (one cell at a time). So I put it into nested For Loops and...

This Macro fills Formulas to Last Row I need it to also fill formats as well Dim LastRow As Long LastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("W4:AA" & LastRow).Formula = Range("W3:AA3").Formula

I have a cell which is referencing a cell in another worksheet, which contains an email address. The source cell formats properly as a hyperlink, but the destination (?) cell does not format as a hyperlink. Is it poissible to have the destination cell be formatted as a hyperlink?

Hi, I need to copy the conditionally formatted cells and paste format to a new range. Currently I do: Workbooks(EnvelopeFile).Worksheets("System Envelopes").Cells.Copy Sheets(xlSheet.name).Cells.PasteSpecial Paste:=xlPasteValues Sheets(xlSheet.name).Cells.PasteSpecial...

Hi there, I have 3 columns of data in the following format: 1234 99 1234567 and I want to get it all into one column but in 2 different formats as such: 1234991234567 and 1234-99-1234567. Would be most grateful for any help

I can do complex spreadsheets but never seem to get the syntax right on conditional formats, especially when it includes a vlookup: Skipping straight to Manage Conditional Formats "IF" statement: 1. I need to say IF the value in A2 = (any value found in Array in Sheet2 column B) then format...

Hello. I am trying to concatenate two cells that contain time of the day and show them as a time slot in text format (like: 09:30-11:00). I have changed the destination formats to text and also used the TEXT formula but it returns strange numbers (fraction of 24 hrs I assume). How can I change...

I am running a query via VBA to populate a number of cells with the results of the query. I have the cells formatted to how I need them but upon the cells being populated, their formats are being changed to date formats. The fields in the Access table are set up with the correct formats and I...

Hi All, So my vlookup formula is referencing from a table where data exists and I am pulling it from the right column. There are no spaces , or additional characters in any cell. What I understand is the issue is that the lookup range and table array may still be in different formats. I...

Hi I have a huge spreadsheet that has evolved over a while and which also included worksheets copied in from other workbooks. As a result, we keep getting the "Too Many Different Cell Formats" message meaning we are unable to apply any additional, new formats to cells without formatting. I...

Hi, Its about 5 years since I did any VBA. How do I copy the contents of one worksheet into another including formats and values ? Here is what I have so far. Thanks Kaps Sub copy_sheet() ActiveSheet.CopyWith ActiveWorkbook .SaveAs Filename:=Environ("TEMP") & "\New1.xls"...

Hi Folks! I am hoping someone can help me out. I have a workbook with 84 tabs which all tabs need to be password protected. I came up with the code below but then I realized that several of the tabs require various other formatting requirements other than AllowFormmattingRows:=True. For...

Hi guys, Hope someone can help me out here. I have a spreadsheet which has conditional formats on it (A9:N9 and down rows below to A700:N700) where depending on what item is entered highlights that certain cells. After awhile the items need to be removed from the spreadsheet, but the formats...

Is there a limit as to how many custom formats Excel can contain? I read somewhere it might be 64,000 (based upon a workbook with 100 sheets or so). Is there a limit to how many custom formats (i.e. fonts, typeface fill, alignment, numeric, borders, conditional formatting) that Excel can hold...

I would like to create a code to check the format of all cells in column A, excluding A1 on my Sheets( "Input") If the format is not one of the following, a msgbox with pop up saying "That number is not valid": Formats ######### ############ ############## ###-###-### ####-####-####...

I'm creating a file that will be sent out to regional offices around the world. We've created a tool that automatically copies pertinent data to a Powerpoint presentation to submit to the corporate office, but I've noticed that all number formats remain in the local currency or date format...

Hello everyone. I have a large macro that has evolved over time and has become quite slow. I'm making an effort to go through the code to clean it up. There are two conditional formats in the macro that apply different conditions to the range A8:I500 based on the left values in column A. I'm...