Copy slection and paste it in ceratian workbook with PART filename as cell value

I've had no luck with a previous post taking data from multiple worksheets.

Sooooo I'm hoping there's another way.

I need to (in a current macro) copy a selected cell and paste it into another WORKBOOK ( I know how to make a macros for normal copy/paste in current workbook) but here is the catch!

I have, everyweek, a macro that saves the workbook AS "WK plus a cell value", wipes the selected ranges and saves again ready for next week.

I need to copy the selected cell E.G B5 in a book called WEEK SUMMARY.

In this book there are 52 spaces for a total amount each week, but i need the macro to save into a relevant cell (week) automatically using the cell value in A1 So if A1=2 then it will paste under WK2(in B2) if its 3 then under WK3(in B3) and so on each week.
Something like:
Const PATH As String = "\\calfp03\folder1\folder2\book1\sheet1 range (A?)....then the value of whatever is in the current workbook of A1"

Then when its week 2 that cell value in original book will be 2 and it will save it into A2, Week 3 will be 3 and so on


So its... copy from one cell, and paste in another workbook with what ever cell number value dictates in the current sheet of A1 ....at the start of this code before it saves.

Please Login or Register  to view this content.

Hi,

here's the code I think you asked for:

Please Login or Register  to view this content.

What it does:
- takes the value in the active cell
- opens up a workbook specified in the "workbooks.open" line
- puts the value into one of the the newly opened workbooks cells on "sheet1"
- the address of that cell is column B, and the column number is specified in the A1 cell in the original workbook&worksheet (where the activecell is located
- it closes the workbook that was opened by the code

I'll give it a try!!!

You told me in PM that it didn't work. How about this one? Please don't forget to change the folder path where indicated.

Please Login or Register  to view this content.

Ok thanks, i'll make up some here as originals are at work.

Ah, I think I see what@s going on now, BUT I have a Range which I need to copy / Take from which would be from sheet called "Summary" B5. But uses Sheet 1 A1 Value to paste this in info in the relevant place which is dictated in A1.

Yes, that's a lot clearer than what I've put haha. The integer number will be on a a different sheet eventually but that's a simple case of renaming it in the macro accordingly.

Hi,

sorry for the long wait, I had things to do. Here's an example code, you can modify it accordingly with proper worksheet names and file path. I added comments in the code for better understanding. Hope it works!

Please Login or Register  to view this content.

Book1.xlsmBook2.xlsm


It starts off, but stops on the PASTE line.

I have attached two sample books, I've swapped cells A+B over so it's easier to follow. Ideally the WK numbers I would like going across rather than down if possible, But vertically would probably be easier in the way of creating a macro?
But it's getting there!!!!!!!


Regards

Here is the code that should finally work, tested in excel 2010. Change the path obviously. Check the file extension too, could be xlsm or xlsx or whatever.

Please Login or Register  to view this content.

And here's the other code that arranges the copied B5 cells in row 2 instead of column B.

Please Login or Register  to view this content.

It kind of works!, It will pastes the Reference (link/sum/whatever) in the cell rather than the VALUE of the copied cell. Because the sheet gets cleared down, the reference is no longer there. Oh joy!!!!! haha

The sheet gets cleared by some other code? Or how? This task seemed so simple in the beginning... :D

Yeah, on a Sunday there is a clear down macro, which.... Saves the work book AS WK plus cell value, it then deletes 4 sheets with data people have put on during the week. When it's deleted, it then saves it as a new sheet ready for the new week. This code I was going to incorporate in the macro before the Save As and clear down, so I could copy over 4 main pieces of data into a week by week sheet. So the pasting needs to be a value now. I did try and put VALUE after paste destination but no hope. Always the things that sound simple seem the worse!!!!

Anyone else with any ideas? I'm still struggling on this one