To Paste special by value

Printable View

08-10-2013, 10:36 PM
ravichandavar

To Paste special by value

HI Team,

I have the macro which opens the files from specified folder and pastes in a file one below another.But the problem is its doing copy paste but i need Paste special by value; Pleas find the below code which I'm using.

PHP Code:

'ThisWorkbook.Worksheets("Data").Clear 'On Error Resume Next Workbooks.Open Filename:=ThisWorkbook.Worksheets("Main").Cells(i, 1) ActiveWorkbook.ActiveSheet.Range("A1:O100").Copy Destination:=ThisWorkbook.Worksheets("Upload").Cells(j, "A") ActiveWorkbook.Close j = j + 100 Application.Wait Now + TimeValue("00:00:05") Next i ThisWorkbook.Worksheets("Data").Visible = False End Sub

Please help me to turn the result in paste special by value.

Thanks in Advance.
08-11-2013, 07:12 AM
Trebor76

Import data as values

Hi ravichandavar,

Try replacing this line...

Code:

ActiveWorkbook.ActiveSheet.Range("A1:O100").Copy Destination:=ThisWorkbook.Worksheets("Upload").Cells(j, "A")

...with these lines:

Code:

ActiveWorkbook.ActiveSheet.Range("A1:O100").Copy ThisWorkbook.Worksheets("Upload").Cells(j, "A").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False

Regards,

Robert
08-11-2013, 10:35 AM
ravichandavar

Hi Robert,

Thanks for the quick solution. Its working fine.

Regards,
Ravi.C
08-11-2013, 02:27 PM
Trebor76

Thanks for the feedback and you're welcome :)
08-12-2013, 10:30 PM
Rick Rothstein

Quote:

Originally Posted by Trebor76

Hi ravichandavar,

Try replacing this line...

Code:

ActiveWorkbook.ActiveSheet.Range("A1:O100").Copy Destination:=ThisWorkbook.Worksheets("Upload").Cells(j, "A")

...with these lines:

Code:

ActiveWorkbook.ActiveSheet.Range("A1:O100").Copy ThisWorkbook.Worksheets("Upload").Cells(j, "A").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False

Alternately, you could do the same thing with this single line of code...

Code:

ThisWorkbook.Worksheets("Upload").Cells(j, "A").Range("A1:O100").Value = ActiveWorkbook.ActiveSheet.Range("A1:O100").Value

I am not 100% sure of how your setup looks, but I am pretty sure you do not need the part of the code I highlighted in red and I am not so sure whether you need the part of your code I highlighted in blue either.
08-13-2013, 02:38 AM
Trebor76

Hi Rick,

Yes, well put. I had considered that code too but thought the original formatting may have been important so I didn't recommend that method. If formatting isn't an issue, then I would recommend Ravi uses your code.

Cheers,

Robert
08-13-2013, 08:00 AM
Rick Rothstein

Quote:

Originally Posted by Trebor76

Hi Rick,

Yes, well put. I had considered that code too but thought the original formatting may have been important so I didn't recommend that method. If formatting isn't an issue, then I would recommend Ravi uses your code.

But you used PasteSpecial and specified xlPasteValues for the Paste argument... only the cell values will get copied with your code, the formatting will be lost. As far as I know, both your 3-line code snippet and my one-liner will produce the same identical output.
08-13-2013, 12:23 PM
Trebor76

Quote:

But you used PasteSpecial and specified xlPasteValues for the Paste argument... only the cell values will get copied with your code, the formatting will be lost. As far as I know, both your 3-line code snippet and my one-liner will produce the same identical output.

Yes, thanks Rick - spot on (again).

Ravi, if you want the formatting as well as the value, use this...

Code:

ActiveWorkbook.ActiveSheet.Range("A1:O100").Copy With ThisWorkbook.Worksheets("Upload").Cells(j, "A") .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats End With Application.CutCopyMode = False

...or if it's just the values use Rick's solution (you can of course still use mine but 1 line is better than 3!!)

Regards,

Robert