Using VBA/Macros to copy/paste in different workbooks

[ghg]

Hi,
I've recorded Macros before but haven't really written VBA code, and I've run into something I don't think I can record. I'd like to create a Macro that copies a block of cells from a fixed location in one workbook and then pastes that table into different workbooks, wherever my active cell is in the 2nd workbook when I run the Macro. Other threads I've read just involve pasting cells to a fixed location in the second workbook, but I'd like to be able to have the Macro paste in different places as I've said, wherever my active cell is at the time. When I tried just recording the actions, the VBA code recorded the specific 2nd workbook file I used that time, but I'd like this to be able to apply to any workbook I open. Any help/guidance would be much appreciated, thanks!

[JBeaucaire]

To help me show you exactly, do this:

1) Open the two workbooks.
2) Set your cursor in the second workbook to the destination
3) Turn on the macro recorder and record your steps switching the other workbook (make you switch sheets too so I can see the sheetname in the second workbook), highlighting and copying the range, switching back to the 2nd wb and pasting.
4) Turn off the recorder and paste that code here in the forum.

Be sure to put [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags around you code here in your post, as per forum rules.


Make sure you provide any information no seen in the recorded data, such as:

- Name of the 1st workbook
j- Name of the sheet in the 1st workbook
- Range of cells to be copied
- Maybe the location on your hard drive of the 1st workbook on the offchance it isn't currently open.


One final question, when you paste into the current workbook, is it just the values you need in those cells, or is it important to have a bunch of formatting travel along in the paste as well? The reason I ask is because you can also get this behavior without ever opening the 1st workbook if we can just insert some formulas in those destination cells.

Be sure to review this post and answer all the questions posed. Thanks.

[ghg]

Thanks so much for your help! I very much appreciate the step-by-step explanations Here is the code I got after recording:

Please Login or Register  to view this content.

I guess I should clarify that I'm not actually Pasting the cells, but inserting them in the 2nd workbook where my active cell is and shifting the other cells down.

1st workbook name = transcriptmacro.xlsm
1st workbook sheet name = Sheet1
Range of cells to be copied in 1st wkbk = A2:I7

Also, it is important to insert the cells with the formulas intact, not just the values, because they will be referencing data from another separate workbook using the VLOOKUP function based off of the ID number in each workbook I'm inserting the block of cells into.

Thanks so much, please let me know if there's anything I haven't explained clearly.

[JBeaucaire]

- Maybe the location on your hard drive of the 1st workbook on the offchance it isn't currently open.

Is this a possible need?

[ghg]

Whoops, sorry:

1st wkbk = C:\Users\ELS\Desktop\transcriptmacro.xlsm

[JBeaucaire]

Something like this then:

Please Login or Register  to view this content.

[ghg]

Beautiful! Thank you so much!!

One more addition though... Would it be possible to add something at the end to change everything I just pasted to values only? That is, once the cells are inserted in the new workbook and the formulas have been applied, then could the macro also copy/paste values only of those cells I just inserted?

[JBeaucaire]

Hmm... maybe:

Please Login or Register  to view this content.

[ghg]

Well, that didn't seem to work... but I can easily record a second macro for that part so no big deal. Thanks again for all your help!

[JBeaucaire]

This should do it:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.