To Paste special by value

[ravichandavar]

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.

'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.

[Trebor76]

Hi ravichandavar,

Try replacing this line...

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

...with these lines:

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

Regards,

Robert

[ravichandavar]

Hi Robert,

Thanks for the quick solution. Its working fine.

Regards,
Ravi.C

[Trebor76]

Thanks for the feedback and you're welcome

[Rick Rothstein]

Hi ravichandavar,

Try replacing this line...

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

...with these lines:

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...

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.

[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

[Rick Rothstein]

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.

[Trebor76]

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...

    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