Macro to pull data from another workbook.

Cherry.

Lets start with the Excel worksheet copy move function and see where where that takes us.

Open your weekly workbook

Right Click on the tab at the bottom of excel for the worksheet that you are interested in.

Select "Move or Copy"

A small window will open.

At the top is a list box It shows the name of your work sheet. Click on that and select "New Book"

At the bottom is a check box that says "Create Copy" Click on that so it becomes a Tick.

New Click on Ok.


Now is that what you need? If not why not? Please post your comments so I can try and help you.

Hi mehmetcik, thanks for responding. The reason why I'm not using the simple move and copy is because some columns/row of data won't be copied over.
I have amended my first post to reflect this.
thanks.

This is the code I have so far.
Both workbooks have the same sheet name. The workbook the data is being pasted from will not have the same name each week. Assume both workbooks will be/are open.
The code is for the workbook that the data will be pasted into.

Please Login or Register  to view this content.

Ok

I think that your copy routine will work with a couple of changes.

Delete this row:-

Please Login or Register  to view this content.

You are not using it in your code and you are confusing a worksheet with a workbook.

Change this formula:-

Please Login or Register  to view this content.

to:-

Please Login or Register  to view this content.

In my test this then worked without corrupting my formulae.

This code works for me You need to change

Please Login or Register  to view this content.

to be your master file name:-

Please Login or Register  to view this content.

Eh? I'm confused by your suggestion. The workbook that data is being copied from should also be open. I want to avoid stating the name of the book because it will change weekly. The workbook it is being pasted into, the name won't change i.e. will stay book3.

Lol.

You run the macro while the new workbook is open and active. [ Make sure that the new one is active by opening that last, or clicking onto it ]

Then the macro will use the command:-

Please Login or Register  to view this content.

to learn its name.

Your master workbook must also be open. or you should modify the macro to open it.