Macro for Copying and pasting from one excel workbook into a series of other workbooks

**Smeddlesboy** · 01-06-2013, 05:05 PM

I am trying to create a Macro that pastes code from one workbook into a list of other workbooks. It's become a bit too complicated.

There is a folder containing many sheets, and rather than update them all individually, I want to be able to copy and paste a few new rows at the end of each one automatically.

In short, there is a Workbook called "Setup.xlsm" in which the cells to be copied and pasted are in range H10:M21 in the sheet "Pupils"
Also in the Workbook called "Setup.xlsm" is a list of all the names of the workbooks needing to be updated. These appear in column B in the sheet "Staff"

The copied cells need to be pasted into column A in each of these workbooks. The user defines which row to paste them in at. The row number is stored in cell M8 inthe "Pupils" sheet of the "setup.xlsm" sheet

The macro will be started from sheet "Setup.xlsm" sheet "Pupils". I need it to open each of the destination workbooks in turn, unprotect it, copy and paste the cells from the other workbook, reprotect the destination workbook, save it and close it.

Sorry the code below might be a bit of a mess, I have been experimenting so much with things I found on Google that I am all mixed up

Please Login or Register  to view this content.

**Smeddlesboy** · 01-06-2013, 07:03 PM

I've done a bit of reprogramming and have eliminated the need to enter the value in cell M8, which was stored as Integer j. I will just always paste the new values into row 1200 and then do an alphabetic sort to move them to the top.

Thought I'd type that in here in case it made suggesting a solution any easier!

**J450n** · 01-06-2013, 08:00 PM

Hi Smeddlesboy,

Does this code work as is? I'm not entirely sure what solution you're looking for, but assume the issue is finding the last empty row of the destination wbk/sheet then pasting and sorting???.

As is, you need to increment i by setting i = i+1 within the do loop before it loops

Regards

**Smeddlesboy** · 01-06-2013, 08:02 PM

No, the code doesnt work as it is. It gets mixed up between which workbook and worksheet it is copying from and which one it is copying to - I can't get it to do the right thing in the right window

**Smeddlesboy** · 01-06-2013, 08:06 PM

Just ran it again, and the line it is getting stuck at is

Please Login or Register  to view this content.

Message is PasteSpecial method of Range class failed

**J450n** · 01-06-2013, 08:33 PM

How about just pasting to range A1200 only???

**J450n** · 01-06-2013, 09:00 PM

Hi again, I've used your code but taken out the subjectnominations bit to test it on my machine. This code works for me - you'll need to put back the correct sheet names for your purposes.

Regards

Please Login or Register  to view this content.

**J450n** · 01-06-2013, 09:35 PM

Whoops, sent you the wrong code in last post as was still WIP!

Try this, have tested and is OK:

Please Login or Register  to view this content.

**Smeddlesboy** · 01-07-2013, 02:35 AM

Thanks. Will give it a go this evening when I'm back from work and will let you know how it goes. thanks for your time.

**Smeddlesboy** · 01-07-2013, 02:13 PM

It's not liking the line

Please Login or Register  to view this content.

Any suggestions what I might have done wrong?

My full code is below. I've also added in a bit at the end that re-sorts the data.

Please Login or Register  to view this content.

**J450n** · 01-08-2013, 02:39 AM

This should work, and as i said, i did test the code and all ok
Are you running the code from the Setup.xlsm workbook or a different file to test it??? Do the "Staff" & "Pupils" sheets exist??? Did you have some different workbooks open when you tested the code as if the "Activeworkbook" was not Setup.xlsm when you ran the code, it would fail as then the sheets wouldn't exist in that workbook. Please try again with only Setup.xlsm (or a copy) open and if any joy.
Failing that, I can only suggest removing the "Thisworkbook" part as shouldn't really need it if this is the only workbook open.

Just another comment regarding the sorting code, It will probably fail as intended because we dont select the workbook opened by the macro. Since we alreeady set it as "ws" suggest you change the code as follows:

Please Login or Register  to view this content.

Regards

J450n

**Smeddlesboy** · 01-08-2013, 02:58 AM

Thanks, will again look at it when back from work. Hopefully its just something as simple as me having had another workbook open at the same time.

though just gave it a quick test now, and when it tries to run

Please Login or Register  to view this content.

It brings up an error, and highlights the ".Sort" in the second last line and describes it as a "Compile Error: Invalid use of property"?

**Smeddlesboy** · 01-08-2013, 02:59 AM

Deleted - posted twice by mistake

**J450n** · 01-08-2013, 07:10 PM

Hi Smelldesboy,

i hope this will put it to bed for you. Let me know how this works out.

Please Login or Register  to view this content.

Regards

J450n

**Smeddlesboy** · 01-08-2013, 07:21 PM

Really sorry, and thanks still for all your effort.

However its not liking the

Please Login or Register  to view this content.

It''s giving me "compile error, invalid or unqualified reference" - and is highlighting the .UsedRange part of the line as the ssource of the problem.

**J450n** · 01-08-2013, 07:31 PM

Ok, change it to this:

Please Login or Register  to view this content.

**Smeddlesboy** · 01-08-2013, 07:44 PM

Ok were definitely getting somewhere now. Making good progress - I can make it work if I disablel one or two bits of the code temporarily, so must mean almost everything is sorted

It still isn't keen on the

Please Login or Register  to view this content.

bit - is there any other way of expressing this code that might improve things?

It also isn't liking

Please Login or Register  to view this content.

- it highlights the Rng and says "sub or function not defined". Again, anything I can tweak there?

All other lines are doing their job and throwing up no error messages

Thanks once more

**J450n** · 01-08-2013, 08:00 PM

for the 1st issue, try to grab the block with this.

Please Login or Register  to view this content.

IF this doesn't work, you can use the bounce up from the final row in column B as used elsewhere ie:

Please Login or Register  to view this content.

then set the range as range("b1:b" & iLR)

as for the key issue, looks like the compiler is converting rng to Rng which doesnt exist hence the problem. I was using the Items method of the range object but maybe got a little lost in the syntax.maybe try rng.range("A1"), or rng.cells(1,4) etc and see how that works

I now see the error of my ways, there are 2 different ways of defining a block being confused here. We can either define a block by range("a1:a10") or else we have to do with 2 fully defined start/end ranges within another range statement with commar separator like range(range("a1"), range("a10")).
for the keys for the sort, just try retyping "Rng" back to "rng". I dont know why it would have got changed. If the compiler is for some reason changing it back to Rng, then best use Ctrl+f and find and replace all instances of Rng to something else. Good luck.

**Smeddlesboy** · 01-09-2013, 02:22 AM

Thanks, am getting somewhere - just need a little more work and I should be there. I am still unclear as to what 'rng' actually is though, as we havent defined 'rng' anywhere? I take it its actually rDest???

**Smeddlesboy** · 01-09-2013, 02:39 AM

Right I can get it to work by deleting a line or two of code and by changing rng to rDest. This is the code I currently have

Please Login or Register  to view this content.

The bits it still isn't liking are

Please Login or Register  to view this content.

It is OK if i delete the key2/key3/key4 bits; but doesn't like it when they are in there. I can't see why, as we have clearly defined rDest to have at least six columns

The other bit it still isn't liking is the very first instance of iLR i.e.

Please Login or Register  to view this content.

where it highights .Rows and tells me its a compile error: invalid or unqualified reference

**J450n** · 01-09-2013, 05:31 AM

Ok, the line "iLR = ThisWorkbook.Sheets(xxxxxx).Range("B" & .Rows.Count).End(xlUp).Row" is missing the sheets reference betwen brackets where i've added the xxxxx's

as for the keys- ????? i've tested this a few times in a blank workbook, just add a say 5 row by 5 row block of random data in the 1st sheet, define it as a range and do the same sort, it all works fine. I'm lost why is not working for you - maybe need to send out a new post to see of someone can help explain.

Yes you were right rng should have been rDest - now you know what i called the range i was testing in a separate workbook!

I don't think i can add much more value to this for you, good luck getting it perfected.

Regards

J450n

**Smeddlesboy** · 01-09-2013, 02:12 PM

I think I finally have it - if I change it to be

Please Login or Register  to view this content.

then it works

I can also make the sorting work if i just choose key1 and leave keys 2-4 for now

Thanks a lot for your help. As you suggest, I will make another post to see if we can get the sorting issue dealt with: you helped me deal with the copying and pasting issue, and thank you very much for that!

Macro for Copying and pasting from one excel workbook into a series of other workbooks

LinkBack

Thread Tools

Rate This Thread

Display

Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Re: Macro for Copying and pasting from one excel workbook into a series of other workbooks

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions