Looping Through Collections

[NeedForExcel]

I am trying to write a code where the loop will run through all the sheets of a template Workbook and copy a certain data and paste it in the Master Workbook.

I am having some issue with a Part of the Code.

Please Login or Register  to view this content.

Can anyone clarify what is wrong?

[:) Sixthsense :)]

Try this... In the below code After performing paste action keep the cursor to the blank cell so that the next paste process will continue in another location. Otherwise the same area will be replaced with the data.

Please Login or Register  to view this content.

[NeedForExcel]

Hey!! Thnx for the revert.

The reason why I did not use "For Each Ws In ActiveWorkbook.Worksheets" is because a different workbook will be open by the time the Macro reaches that part,.

Below is the whole Macro and related files -

Please Login or Register  to view this content.

Please help me with the issue, as i am trying to solve it since a long time..

Help appreciated!

[NeedForExcel]

@ SixthSense

I have changed the code. The code is running however it is not running through all the worksheets in a workbook. I have to manually run the Macro again for the next worksheet.

Secondly it is extremely slow. Can you suggest something to increase the speed?

Please Login or Register  to view this content.

[Norie]

Why are opening, closing and then opening the master workbook?

[NeedForExcel]

If I close the Master workbook, then I can refer the Template workbook as the activeworkbook. Thats the only reason.

Id be grateful if you can suggest a better way.

I have also shifter the Macro to the Personal Macro Workbook, hence only 2 files will be required. Master Bom and Template (refer Attachment in Post #3)

[Norie]

Don't use ActiveWorkbook, refer to the template by it's name or, and probably better, create a reference to it.

Which workbook is the code in?

If it's in the template then you can use ThisWorkbook to refer to it.

[NeedForExcel]

I used Activeworkbook as there are 100z such templates.

However, I worked on it and was finally able to get it working..

Please see the sheets attached. The code is the Upload.xlsm sheet.

If possible please guide how can I get the code to work even faster?

Thanx a ton for the reply.

[Norie]

I don't see anything like the code you posted in the workbook Upload.xlsm.

[NeedForExcel]

I changed it coz the 1st code was incorrect.

But it basically does what I wanted to achieve in the 1st code I stated above.

Is the new code alright?

[Norie]

What code should I be looking at?

[NeedForExcel]

Here is the code. I am just trying to know if I can make the Code better/faster?

Please Login or Register  to view this content.

[Norie]

This seems quite fast, probably because it only opens 'Download.xlsm' once.

Please Login or Register  to view this content.

[NeedForExcel]

Thats amazing.. Thanx..

[NeedForExcel]

Just 1 Query -

Please Login or Register  to view this content.

Referring to the above 2 lines where you set the Workbook, and the Download.xlsm Workbook opens.

In the next line when you mention Thisworkbook, will it not refer to Download.xlsm as it the the activeworkbook?

[benishiryo]

looks like you got it solved. anyway, i'm not someone you should pin your hopes on when it comes to VBA. "ThisWorkbook" always refers to the workbook where the codes are stored, not the ActiveWorkbook.

[NeedForExcel]

Perfect!! I had a misunderstanding regarding the subject.

Now it is sorted out..

[NeedForExcel]

@ Norie

Hi!!

I am facing a problem. The Code you wrote is working fine. I tried it on the 1st workbook and it worked perfectly.

However I am not able to run the same code on other similar Workbooks.

Please Login or Register  to view this content.

I have a Mastersheet where the data is copied and pasted. And the Templates keep on changing. What I am doing right now is Open the template workbook, make a COde Module, Paste the code and run the Macro.

Can you help me out??