Copy Data from multiple workbooks to a master Workbook

[zeeez]

I've been skimming through the majority of the forum to get the code that I require but currently none of the coding does what I want!
Currently I have multiple workbooks, stored in a folder, with different names, all of which contain one worksheet called Database, (his sheet is currently hidden using the xlveryhidden command, the range to which data is logged is constantly changing, it begins from B3:AD3 to an arbitrary number call it x
I would like to do the following
1) Open each workbook located in that directory
2) Select the active range (only the populated cells) so B3:AD3 to x
3) Copy to the next active cell in a masterwork book called ReportPivot
4) Close the workbook
5) Loop for the next until it’s finished
The issue is each workbook/sheet is protected or hidden using vba, so how can I overcome this obstacle, only thing I can think of is un-protecting/hiding the workbook/sheets
Any help is greatly appreciated

Kind Regards
Zeee

[JBeaucaire]

Yes, you can open the workbook, unprotect, unhide sheets (do you need to do this just copy data?), then close the workbook(s) without saving changes.


Skimming through the forum you should be collecting the bits of code that each resolve each step of your need and assembling your macro yourself. If you do not, then you'll never understand the macro which is a must if you're going to use it ongoing.

[zeeez]

Hi JBeaucaire

Thank you for the quick response

I got elements of coding, and whilst it does open the file it returns an error upon copying! I've tried multiple posts and whilst the coding provided by the members of the forum where useful but it wasn't giving the desired effect, I've come here as a last ditch effort! I can't seem to change the coding to meet my particular requirements (looking at the last active cell in the database that is to be copied).

Kind Regards

Zeeez

[eemiller1997]

2) Select the active range (only the populated cells) so B3:AD3 to x

Have you used keyboard shortcuts to do this part? that way, no matter the amount of data, you always get all of it. It may be the part of the code that is collecting the data is what is causing the error. Ctrl+Shift+right arrow will select content all the way to the right, and then Ctrl+Shift+down arrow will select content all the way to the bottom. So, if you test this by creating a macro, by placing your curser where you'll always want the macro to start, I got the following code:

Please Login or Register  to view this content.

Does this help any? I'm not sure where exactly you error is in your code. Perhaps you should post your code.

[zeeez]

Thank you for the coding you provided! That is essentially an element of what I want but I want excel to determine where the data ends, not user-input.

As to why I haven't copied the code, it was bits and pieces of what I've found on the forum! I want a so called "Generic" code that I can then adapt to my system, each person provided a specific solution to their problems. I have multiple uses for it, hence why the parameters have been left undefined as x etc etc

Kind Regards


Zeeez

[eemiller1997]

I am new at this. Can you help me understand the difference between where data ends and user-input ends?

[zeeez]

Hi eemiller1997

There is command in vba which checks the last active row (the last row with any data inside of it), at least to my understanding that's what it does.

[eemiller1997]

Does this help

One common method


LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row


which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.


Another method to find the last used row in a particular column is:


LastRowColA = Range("A65536").End(xlUp).Row


but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.


A couple extra methods are more reliable.


LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
or
LastRow = ActiveSheet.UsedRange.Rows.Count


This methods can be used on any sheet, not just the active sheet.