VBA Code to copy cell range and paste to certain sheet

hello all!

this is my first post here, i`ve been dabbling in VBA coding for simple and medium macros, but right now i really need some help. here`s what i`m trying to do:

find a range of cells in some sheet, based on the string in column "B" then, i want all the values in the range E-P (in the "infosheet" tab) to be copied to a summary sheet in a range C-M (sheet "summary sheet 2" in the example). i know, it sounds vague, but i will attach an example workbook, to clarify things.

once i get this down, i am sure i can tweak it to do the same for some other totals i need in yet another sheet ("summary sheet 1")

please bare in mind that the range of the source table (sheet "infosheet" in the example) is dynamic (might have 70 rows this month, 60 or 80 next month).

any kind of helo will be greatly appreciated.

cheers!!

examplesheet.xlsx

Do you need macro or formula .

Originally Posted by kvsrinivasamurthy

Do you need macro or formula .

hi, i would like this as a macro, but if you can provide a formula which i can also use and integrate into a macro, that would also work.

right now i`m looking to save some time (been trying to get this to work for about 4 days now).

thanks!!

Maybe:

Please Login or Register  to view this content.

will test this and provide feedback momentarily.

cheers!

tried it, tweaked it for my own special needs. after that it did what it was supposed to do, but i need the "paste" in the "summary sheet 2" to be special->values.

also, could you include some reference there as to the sheet it's extracting the data from? i will need to take this through 8 other sheets, and i don't want the same data pasted in the entire table.

something like:

Please Login or Register  to view this content.

that would be really helpful.

for the most part i prefer to stay away from automatic copying, but going back and forth between 9 sheets + copy/paste it takes a lot of time that can be saved by doing this.

thank you!!

the above code pastes the copied contents with the background formatting, and i don't need that. moreover, it deletes the row below, like in the picture. all i need is for it to get the values and paste them in there.

1.JPG

cheers!

Maybe:

Please Login or Register  to view this content.

Mr.Davis, thank you very much for taking the time to do this.

unfortunately, for some strange reason, the copying is done, it copies the exact thing that i need, but, when it pastes the info to the target area, all it pastes is 0(zero), for the entire range. the info that is copied is part of a subtotal formula, so, it should work, when pasting only the values.

i have manually pasted the info in the clipboard and it is accurate. so, something must be amiss with the paste operation. any ideas?

cheers!

When I tested it, it worked? Can you provide a sample with a more accurate depiction of what you are trying to do?

as far as i can tell, all it does is place the cursor in the "infosheet" at the end of the used range on column b. that's it.

From your last example if you clear out the fields in your Summary Sheet 2. When I tested it, if there was a "Subtotal" in Column B, then it copied the requested data to Summary Sheet 2? Maybe I still don't understand what you want.