Paste special as formats with array value 2

[UnsolvedCoding]

I am having issues getting formatting correct in a workbook.

Here is the exact code

myArray = ThisWorkbook.Sheets("Raw Data").Range("A" & i & ":Q" & J).Value
Workbooks(sWorkbook).Worksheets(sSheetName).Range("A2:Q" & J + 2 - i).Value = myArray

The code works perfectly but when it bgets put into the new workbook the formating screws up. What I want to do is paste special as formats if thats possible.

Does anyone know if that can be done with the code given?

 

[UnsolvedCoding]

This is an example of what I am trying to fix.

The number shows as something like 3.60126E+11 instead of being a complete number.

 

[SkipVought]

hi,

FLOAT is inexact as far as precision goes. Check out in wiki.

By "complete number" do you mean an INTEGER value?

If you want to PASTE, then use PasteSpecial xlpasteformats.

If you want to assign a number format, then use the NumberFormat property.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

The example given is from an invoice number. Some of the invoices start with letters, some are all numbers, some are hyphenated with letters and numbers mixed together.

If possible I want to avoid using paste but need to retain the original formatting somehow.

 

[SkipVought]

FIRST assign the format of the range as TEXT, then assign the value

with Workbooks(sWorkbook).Worksheets(sSheetName).Range("A2:Q" & J + 2 - i)
   .numberformat = "@"
   .Value = myArray
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Why not simply this ?

ThisWorkbook.Sheets("Raw Data").Range("A" & i & ":Q" & J).Copy Workbooks(sWorkbook).Worksheets(sSheetName).Range("A2:Q" & J + 2 - i)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[UnsolvedCoding]

Thanks to both Skip and PHV