Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










VBA PasteSpecial Format & Values


I am trying to copy a range from one sheet and paste in another sheet via VBA.

Sheets("RECAP CURRENT YEAR").Select
Range("E:E").Copy
Sheets("FORECAST").Activate
ActiveSheet.Paste Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)
Range("A1").Select
Column E has formulas (=SumB3:D3) nothing more then that. I get a #REF after the paste into the FORCAST sheet.

I would like to Paste Values and Formats.

I tried changing the code to this, with different variations:

Sheets("RECAP CURRENT YEAR").Select
Range("E:E").Copy
Sheets("FORECAST").Activate
ActiveSheet.PasteSpecial Paste:=xlPasteFormats, xlPasteValues _
Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)
Range("A1").Select

But keep comming up with errors.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
PasteSpecial Format CSV From Recordset
Currently, my code involves making a connection to a db then run queries. Results from the queries goes to recordset & from the recordset copy to specified range in excel worksheet. I am trying to do an automation process.

The problem is that once the record is pasted in excel worksheet, the date column is not being recognised as date therefore excel function (vlookup) is not giving me the results in my report.

When i do a manual PasteSpecail as CSV into the worksheet from the query result, my report gets populated with data which is correct.

i've tried doing a pastespecial format:="CSV" but it doesnt work.

Is there any codes that i can use to copy from the recordset as a CSV format pastespecial??

Or any other ways that can be done?

View Replies!   View Related
VBA For PasteSpecial Values ONLY?
I was wondering if there is some VBA code that I can put in my spreadsheet that would only allow Paste Special Values?

I want to make sure that the user can not simply cut and paste. If possible it would be nice if the user doesn't know what is going on all they need to know is that they can copy and paste.

Is there a seamless way to accomplish this?

View Replies!   View Related
Pastespecial Formulas & Formats Pastes Values
The problem: I am getting values when doing a pastespecial for formulas and formats.

Manually copying and doing a pastespecial with the mouse gives me the correct results. Below is code I got from the forum, "here", and am running in a test workbook.

Option Explicit
Sub Test2()

'
Dim rSource As Excel. Range
Dim rDestination As Excel.Range
Set rSource = ActiveSheet.Range("A1:C1")
Set rDestination = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0)

rSource.Copy
rDestination.Select

Selection.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Selection.PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Range("A1").Select

Application.CutCopyMode = False

valKill:
Set rSource = Nothing
Set rDestination = Nothing

Exit Sub

End Sub

Does anyone know why I am getting values? What do I need to do to make this paste only the formats and formulas?

View Replies!   View Related
Copy Pastespecial Loop
The following macros will:

I'm desperate for a copy paste macros that will:

1) Copy H3:H143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on D4:D144 (141 rows)

2) Copy I:3:I143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on I4:I144 (141 rows)

So, in essence, it will copy every single column across on "Sheet 1", but will paste every 5 columns on "Sheet 2" .

It will then loop 33 times to continually copy and paste across columns.

View Replies!   View Related
Copy PasteSpecial Not Working
I've just written a new Excel application which works fine when run on my local PC, and also when I save a copy on a central network server, and run it over the network.

However when another user who's testing it, runs it from the same network server, or on his local PC, the code trips out at the following line of ....

View Replies!   View Related
PasteSpecial Method Failed
I have a working macro that duplicates the active worksheet with values only. It basically cleans out formulas and data validation from the data. The code is:

View Replies!   View Related
Pastespecial Of Range Class Failed!
I wrote the following macro to copy some values from a master workbook to a new one. It works superb on my small test sheet but once I try to implement this on my big mastersheet I only get the "Pastespecial of range class failed" on the second pastespecial operation. Why does it work on my small test sheet and not my big master sheet?

Sub ReportGenerator()
Dim NewWorkbookFileName As String
NewWorkbookFileName = ActiveSheet.Name & " report" & " as of " & ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
'Debug.Print NewWorkbookFileName
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add xlWBATWorksheet
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("a1").Select
Application.GetSaveAsFilename (NewWorkbookFileName)
End Sub

View Replies!   View Related
Copy & PasteSpecial Failed Error
This is the code I have:

Range("A" & rr).Copy
Range(Cells(rr, 1), Cells(rr, 38)).Delete Shift:=xlUp
Range("A" & rr).Select
ActiveCell.PasteSpecial xlValues

this code, should, find a cell in row A, copy the contents, then delete the whole row, and place the contents in the cell it lands on after the deletion.

But i get a: 'PasteSpecial method of range class failed'

View Replies!   View Related
Run-Time Error 1004 PasteSpecial Method
I am having trouble with a macro that is giving me the error Run-Time error 1004 (Paste Method of Worksheet Class Failed)

The code is

Sub getfile5()

Call getfile(2)

Dim ie As Object
Set ie = CreateObject("internetexplorer.application")

Windows("TRANS CHECKS CALENDAR.XLS").Activate
Sheets(2).Select
ActiveSheet.Paste
Range("A1").Select

' ie.Visible = False

Windows("TRANS CHECKS CALENDAR.XLS").Activate
Sheets("Main").Select
Range("A1").Select

End Sub

The macro is timing out on the ActiveSheet.Paste entry...The funny thing is that a number of us can get the macro to work and several of us are getting this error...I am thinking that it is a setting in excel that is causing this...

View Replies!   View Related
Pastespecial Method Of Range Class Failed
I have written some code to move data from one sheet to another. Since the from sheet has formulas, I use the PasteSpecial command. I have used code like this for years, and all the sudden, this starts breaking. And, here is the fun part, I run the code and it works sometimes. I never know when it will fail. It is so random. This is killing me. I have tried to create objRange object and assign them and it works sometime and fails others. Also, I tried adding the line Worksheets("Daily Dashboard"). Range ("C72").Select before the first PasteSpecial as to select the cell first before pasting. Then I get the "Select method of range class failed".
Lastly, I tried copying the code from behind a worksheet into a new module. The code is triggered by a button on the first worksheet. Still fails.

Private Sub cmdGetData_Click()
[Result1].Value = ""
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Worksheets("Calculation Sheet").Range("A39:A62").Copy
Worksheets("Daily Dashboard").Range("C72").PasteSpecial xlPasteValues
Worksheets("Calculation Sheet").Range("C39:C62").Copy
Worksheets("Daily Dashboard").Range("E72").PasteSpecial xlPasteValues

[Result1].Value = "Complete"
Application.Calculation = xlCalculationAutomatic
Sheets("Control Panel").Select
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
Error :: PasteSpecial Method Of Range Class Failed
I found the following for something that I was searching for on the web.

Selection.Columns.PasteSpecial Paste:=8

My question is two fold, 1) What does the '8' mean? 2) Is there someplace that tells me what other numbers for PasteSpecial mean?

This solved a problem that I had when trying to do a PasteSpecial for Column Width. What I had been trying was the following:

Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

But I would get the error run time error '1004', PasteSpecial method of Range class failed.

View Replies!   View Related
PasteSpecial Error: Method Of Range Class Failed
Check Personnel Number in Data Tab with Personnel Numbers in Insert Tab. If they match copy that row from Insert Tab and paste it into the next available row in the Moves Tab.

Column Descriptions: Name, Age, Phone Number, Personnel Number, Notes

Worsheet Tabs: Data, Insert, Moves ....

View Replies!   View Related
Run-time Error 'PasteSpecial Method Of Worksheet Class Failed'
I have a pretty simple macro that I recorded and attached to a button. The macro is:
Sub Paste_Data()

Cells.Select
Selection.ClearContents
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Range("A3").Select
End Sub

The user opens the file that contains this macro, then runs a report from a website that dumps into an excel file. They copy the data from the Book1 output, then click the button to paste it into the template. If done this way, it works fine.

However, if they run the report and get Book1 THEN open the file containing the macro, they get a run-time error 'PasteSpecial method of Worksheet class failed' on the 'ActiveSheet.PasteSpecial... line

View Replies!   View Related
Runtime Error 1004 Pastespecial Method Of Range Class Failed
I am encountering this error when I try to run code I have written on my own machine. I am working with all unprotected sheets, and I am running Excel 2007. All of the sheet exist.

Here is my ....

View Replies!   View Related
Run Time Error '1004': PasteSpecial Method Of Range Class Failed
This file has been running just fine....

But now I got this

Run Time error '1004':
PasteSpecial method of Range Class failed.

View Replies!   View Related
Run-time Error '1004' - PasteSpecial Method Of Range Class Failed ....
This very simple macro in Personal.xlsb is driving me crazy!

I want to paste a previously selected and copied range at the current position as values. The range will seldom be the same as previously, and the position where it will be posted will be random too.

The code is: ...

View Replies!   View Related
Run-time Error '1004' Pastespecial Method Of Range Class Failed
I am using the following code and I'm getting a Run-time error '1004' error. When I reconstruct the macro one line at a time and run the macro between adding each new line - no error. After reconstructing the macro in its entirety, I can run it once with no error. However, if I try to run it again immediately after that, I get the error and I keep getting the error every time I run it from there on. I dont understand how it can work once and then stop working. Here is the full

Sub MoveToRoster()
ActiveSheet. Unprotect
Dim item As Long
Dim myString1 As String
Dim myString2 As String
Dim myString3 As String
item = InputBox("Please Confirm The Row Number Of The Child To Be Moved To The Roster.")
myString1 = "c" & item & ":e" & item
myString2 = "g" & item & ":n" & item
myString3 = "c" & item & ":e" & item & ",g" & item & ":p" & item ..........................

View Replies!   View Related
Error 1004: Pastespecial Method Of Range Class Failed, Macro Works Sometimes
I have a macro that works by pasting formulas into cells and then pasting over them with the values produced by the formulas. It works fine for 500 iterations then crashes at this line:

View Replies!   View Related
Copy Format & Values Only To New Worksheet
This code works great except I would like it to copy the format and values only (no formulas) to additional worksheets that are named based on a cell value. What's missing?

View Replies!   View Related
Conditional Format IF Cell Is One Of Three Values
Cell D8 contains many different account codes. When entering into this sheet, if D8 is filled using account code 4010.12, 4010.17, or 1190.1000, then I want Cell J8 to turn a different color, flagging that cell so they know they have to fill that cell in J8, otherwise that cell will remain blank.

View Replies!   View Related
Custom Format (insignificant Values)
I am currently using this format to display numbers within a pivot table (this put's in a comma and removes any decimal place):

_(* #,##0_);[Red]_(* (#,##0);_(* "-"??_);_(@_)
Scenario1-If the cell evaluated is 0>1 the cell displays: 0 (a black 0)
Scenario2-If the cell evaluated is 01 are insignificant and I would like them to remain as a black - so as not to draw attention to them while evaluating data within the PT.

Does anyone know of a way to display a black - in all three scenarios above short of editing the original data?

I was hoping there was a way to do this via the custom format option or within the pivot table as the data that produces the PT should remain untouched.

View Replies!   View Related
Conditional Format MAXIMUM Values
Is it possible to apply a Conditional Format to the MAX Value in each Column and color the appropriate Name in ONE SHOT !? What I mean is - selecting range A2:D9 and applying a C.F. I managed to achieve that by applying two different Conditional Formats. Three separate conditions for Col. A and one condition for the remaining 3 columns (B,C,D).

View Replies!   View Related
Format Textboxes According To Cell Values
I've got a UserForm that contains 31 TextBoxes ( Named Text1, Text2, Text3 ... Text31). I want to format the Textboxes depending on the values stored in a Sheet - these values are stored in the first 31 rows of the Sheet and each row coresponds to only one TextBox. I need to know whether there is a way for the code to recognize that the values for the first row influance the format of the first TextBox (TextBox1), the second row influences TextBox2 and so on and so forth. The code should then set a range and assign each of the 31 rows to each of the 31 Buttons. Therefore the question - can a part of the name of a TextBox become a variable and be mached with the coresponding Row Number?

View Replies!   View Related
Conditional Format - Based On A Series Of Values
what I am trying to do:

I have a workbook with 2 sheets.

Sheet "A" has a column called "FILEPATH" that contains the complete path to a file (eg. "C:mystuffdocumentsdocA.pdf"). There are about 3500 rows of filepaths on this sheet.

Sheet "B" has a column called "DELETED FILES" that contains a list of deleted filenames (eg. docA.pdf, docC.pdf, etc.), one filename in each cell.

I want to highlight the rows on Sheet "A" that contain a path to a file that is listed as deleted on Sheet "B".

View Replies!   View Related
Conditionally Format First Cell Based On Values In The Row
i've been trying for teh last two days to figuire out a way to conditionally format the first cell in a row to turn a certain color based on the values of several independant cells in that row, as opposed to monitoring the whole row. I have tried numerous IF, AND, OR, formulas to no avail. The cell obviously also needs to ignore blank cells...Can this be done?

Basically I want the first cell to turn yellow if dates in certain cells are within 90 days of today, and turn red if within 30 days of today.......

View Replies!   View Related
Date Format Lost With Paste Special-Values
Using 26th January 2007 as an example

I have formatted a cell to show this as dd 26.

When I copy and paste it to another workbork where I needed it shown as a number it appears as 39108.

Is there an easy way to convert the Convert Date Format?

View Replies!   View Related
Format Cells Based On Values In Pivot Table
I have a pivot table and next to it I added some calculations which are based on data from the table. In my "real" file, the cells next to the PT are formatted with conditional formatting based on other values in the sheet (see attached sample file). What I now need to do is add borders to the cells in columns D:G only if the cells in B5 and downwards are not empty. I have tried doing it with conditional formatting but there are some other rules which contradict this setting.

The idea is to make the cells in columns D:G look like they are a natural extension on the PT. However, the table can grow or shrink (in length) and I want to see borders only around cells that are on the same rows as the data in column B (staring with B5). I think that whatever solution you can provide (if you can...) should be based a workbook_change event, because in my real file, the length of the table is changed whenever I select a different page of the PT.

View Replies!   View Related
Another Paste: "Pastespecial Method Of Range Class Fail"
I know its not monday but I'm having a monday like problem. When I try to run this code it gives me "Pastespecial method of range class fail".

View Replies!   View Related
Userform Date Format Reverting Back To Us Format On Change Event
I have created a userform but I am having extensive problems with the date formats.

My system is set to UK and short date is set to: DD/MM/YYYY

When I used code to add the values in the userform to the spreadsheet, any that contained a date format would revert to the US format.

So I finally figured out to use DateValue to format it correctly for example: ...

View Replies!   View Related
Convert Cells With Percentage Format To 'same' Decimal In A Column With Mixed Format
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.

View Replies!   View Related
Save Conditional Format Colour But Delete Condition Format
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.

I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.

Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.

View Replies!   View Related
Change Cell Format Based On Existing Currency Format
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?

View Replies!   View Related
Time Format: Format Cells To Contain Minutes, Seconds And Hundredths
How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09

View Replies!   View Related
Format Climatic Data From A Weather Station Into A Desired Format
I need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.

I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.

View Replies!   View Related
Vlookup Function: Re-format The Information Into A Horizontal Format By Week
I have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.

View Replies!   View Related
Format Email Generated By Sheet To Have An HTML Format
I'm trying to format my email generated of excel sheet to have an HTML formal before they are sent out using MS Outlook.

I have googled, searched your forums and also tried to figure out a solution with the existing helps on the internet. I need help on how to format emails genarated out of my excel sheet. I know a bit of HTML syntax but a newbie to VBA. I'm guessing the formatting has to be done in the Emailbody text function in my code.

I'm also trying to understand the logic behind the code and also learning excel vba bit by bit at the minute.

View Replies!   View Related
Change A Date In Text Format Into Regular Format
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.


View Replies!   View Related
Save Number As Text In Txt Format Wo Changing The Format
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.

First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.

View Replies!   View Related
Pasted European Date Format Coverting To US Format
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another

18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK

View Replies!   View Related
Format Cell: Changing The Format Of Sheet2 To Exactly Like The Following Image Below
I need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.

View Replies!   View Related
VBA: Format Amts Into Text $ 9.99 Type Format
Need to create a set of aligned text amts from various cells...Tried Format but unable to get right combination...I've looked at many threads and most seem related to getting amt from text instead of reverse.

Cell may contain nothing or a monetary amt, negative or positive. Output needs to be in format of "$9,999.99-" or similar...with leading zeros suppressed but a min of "$ 0.00" showing, so that above/below amts with be decimal point aligned in a fixed font situation.

I've almost gotten my routine finsihed but this is last remaining obstacle.

View Replies!   View Related
Convert Text Format To Numbers Format In Vb
how i convert text format into numbers format in vb.
Currently

i have a formula in vB:

Private Sub Textbox3_Change()
Textbox3 = Val(Textbox1.Value) + Val(Textbox2.Value)
End Sub

however..when i sum it up (in excel) using"=sum" formula...it ooes not sum up

I faced an error "number stored as text"..how to i convert it to numbers format in vb.

View Replies!   View Related
Format Number To Text With Specific Format
I'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:

474556788 to 047-455678-8

Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)

View Replies!   View Related
Format Currency As Correct German Format
I have a spreadsheet which needs to display currency in the correct format dependant upon whether a contract is in English or German Law. Excel defaults currency format as 310,000.00 however in German this needs to be 310.000,00 i.e. with the decimal point and comma in opposite positions. Does anyone know what expression I can use to format it as the latter?

View Replies!   View Related
Convert A Date Format To A Text Format
Example......

In D2 I have: =B2
In D3 I have manually input: (space)3-08

As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!

View Replies!   View Related
Date Format: Change Format To Mm/dd/yyyy
I have txtboxes in which I write date in format dd/mm/yyyy but if I want to put date in cell correctly from this textbox in need to declare event on exit -change format to mm/dd/yyyy and then it puts date in desired format which is dd/mm/yyyy and my regional settings are English(uk)

Is there a way to put any date format in textbox and in cell there will always be format dd/mm/yyyy or at least if date is entered in format dd/mm/yyyy that also in cell the same format is putted

View Replies!   View Related
Converting? Format To Text Format
On the attached example i have a list of fractions (in the format ?/???). However, i have a problem when the fraction is 6/4 or 4/6 as excel rounds the nominator and de-nominator down to the lowest value so these fractions become 3/2 and 2/3. For these two fractions only i don't want this rounding down to happen.

I am open to any suggestions, but i was thinking of some vba where on pressing a button it would run a macro that went through the cells in the column and if the cell value is 1.5 (3/2) then format the cell as text and enter the value"6/4". For the 2/3 fraction, i think you may need to say if the cell is greater than 0.66 and less than 0.67 because this would possibly be harder to exact as the number of decimal places involved.

View Replies!   View Related
Format Different Numbers To The Same Format (as Text)
How to format different numbers to the same format (as text). Mainly I need code that would format numbers like 25, 25.36 or 254.60 to numbers looking like this 000002500, 000002536, 000025460. They must be of nine digit length with the last two digits as decimals.

View Replies!   View Related
Convert Date Format To Another Format
How do I change a date "10/01/2004" into a format like 20041001? I used concatenate(right(A1,4),left(A1,2),mid(A1,4,2)) but the function takes the serial value of the date as an argument and concatenates that . So I get a different value.How can I get away with it?

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved