copy Range in Value & Paste Special form.

Hello Experts' & Guru's

We are using a VBA code in my project file for copy a Range ("A115:U215") from one sheet to another sheet (prarup-1) when A specif cell value "Y"

part of code line in below

Please Login or Register  to view this content.

We want in Above Range A115:I215 & N115:U215 in Value & J115 to M215 in Paste Special Form..... pls suggest me where & what i change in above code

Thanks in advance

Hi, santosh226001,

maybe indicate where to put the values as I doubt they all should be placed into the very same area. And you would need to epxlain if any further action is to be made for these cells as well.

The code may look like (untested)

Please Login or Register  to view this content.

Ciao,
Holger

hello Holge thanks for reply

when u add this lines in my code as follwing

Please Login or Register  to view this content.

Thank this msg disply in run time

"Run-time error'1004'
Paste Special method of Range Class Failed


Sir in previous code Resize(101, 21) but u suggested Resize(1, 9)


& Sorry also when we chek we error range is A115 to U215 ... sorry sorry plss... it's not a single row data it's 100 rows data.. pls..

Hi, santosh226001,

please have a look at the Forum Rules (especially 6a: Never edit a thread or post to which others have already responded).

Originally Posted by HaHoBe

indicate where to put the values as I doubt they all should be placed into the very same area

Nice still to have no information about that because that may help finding a solution.

What about a workbook for testing? I must admit that the offset is wrong (should adapt the ranges properly and not be 1 but at least 10 or even more if your areas are wider). And about the pastespecial: usually itīs the first cell which is given as destination. But somebody certainly "tailored" that code for a reason unknown to me so maybe use 2 With-Staments instead of just one to take care of both ranges for values as well as a single cell for pastespecial.

range is A115 to U215 ... sorry sorry plss... it's not a single row data it's 100 rows data.

Hm - 115 to 215 makes 101 rows for me.

Ciao,
Holger

Hi, santosh226001,

what formulas as there are none in the sample in the area you want excluded from values? If itīs a sum in Column N for the cells in the left of that column I would add them like the sum at the bottom of the copied range. It really would help me if the sample and the result would reflect what you are after instead of guessing what it might or not be.

Ciao,
Holger

Hi, santosh226001,

maybe try this alteration where I used a new variable to hold the first empty row in Sheet Prarub-1:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, santosh226001,

Please Login or Register  to view this content.

CIao,
Holger