Excel: Macro to copy new ranges into a sheet?

Question

I have a bunch of excel workbooks with user data in them that I need to put into one workbook that will simply be a compilation list. I need a macro VBA code that will take the user data range and paste it onto this large sheet.

Some features that I'd like to have too - when the macro puts the data on the new sheet I would like it to realize where the last active row filled with information was and fill the next blank row (instead of deleting any of the prior rows), since it will involve many sheets (200) all updating onto this one sheet.

On the individual sheets I would like there to be a button that will activate the macro when clicked (although not necessarily right now, the above is most important).

Answer 1

a few questions before i start
1. user data range, is it always the same block of cells? 1 block or many block of cell? on single sheet or multi-sheet workbook? selected by user before getting macro to summary sheet?
2. the summary workbook, does it has only one worksheet? name tag of sheet stay the same all the time? want to put all 200 to 1 new workbook each time or keep adding to the old summary workbook?
3. are you prepare to add the code to these 200 workbook or put code only in summary sheet?

Answer 2

That's not an easy feat to accomplish, but it can be done. The problem lies in regards to several things:

1. We don't know the name of your workbooks.
2. We don't know the size (rows and columns) of your data for either where the data is coming from or where it is going to.
3. We don't know the name of the worksheets.

I can give you some steps to get you in the right direction. But without knowing some vital data it is almost impossible to write VBA code that will accomplish that.

There are a few things that I'd recommend:

1. Put all of those workbooks that contain that data in the same folder. It makes it a lot easier for the macro to search for the data. Otherwise the macro is going to need all of the workbook names or some way to search for all of those workbooks.

2. Name the data ranges that you want copied all as the same name. If you have that data on more than one worksheet in a workbook, then you'll want to specify the range name as a worksheet level named range. That means when you name it you put the worksheet name in front of it. For example, if you had a range you want to call Data1, to make it a worsheet level named range on Sheet1 you'd enter this in the name box after selecting the range:

Sheet1!Data1

When you select the range later it will say just Data1.

3. I'd recommend transferring that data to an array then transferring it from the array to the worksheet you want the data transferred to. If you don't use arrays, it is going to take REALLY long for that macro to run and you computer will be froze up for a while. Transferring data to an array is done like this:

Dim MyArray As Variant

MyArray = Data1.Value

You will need to use UBound, Count, Column, UsedRange, and Row to figure out the size of the array, to figure out the last used row in your filled workbook and a bunch of other stuff.

Adding the button to activate the macro is the easiest part. To do that, just open the Control Toolbox toolbar and insert a command button on the worksheet. Right click the command button and click View Code. That's where you can put the code for your macro. Or you can write the code in a module and use a Call or Run command to activate the macro code.

If you haven't written VBA macro code before you are probably in over your head. If you'd like to learn programming in VBA to write the macro, I'd recommen John Walkenbach's book: Excel 2003 Power Programming with VBA. It will help you be able to understand how to do what you are wanting to do.

Answer 3

right it extremely is yet another short macro: Sub Spl_CopyPaste() Dim i, j j = 4 For i = a million To a hundred Cells(i, "A").replica trip spot:= Sheets("Preview").variety("B" & j) j = j + 2 next i end Sub