Help with Excel

[TDT]

Hi guys. I need some help with something I am trying to do in Excel. Here's the scenario:

 

I have a few sheets (all named Sheet1, Sheet2, etc.) that have the same data structure (they are generated monthly from another app) and I need to consolidate all the data from those sheets into one sheet with the same columns. I'm not sure what's the ideal way to do this, either with Consolidate/Concatenate or via a macro, but the biggest problem is that the number of sheets is different each month (like, this month I have 5 sheets, next month 7, etc.) and I really don't know if it's possible to automate what I'm trying to do.

 

Any Excel experts here?

[vsny]

Interesting problem.  I'm pretty good with excel and I like excel challenges, but I don't have an immediate answer.

 

I think if you want to make it look nice you'll have to use a macro.  It will be much more flexible and allow you do filtering, sorting, custom calcs, etc.

 

I *think* at least you'll need a macro to list the sheets names that are available.  Then formulas that refer to those sheet names.  Either way I think there will be macros.

 

Do you know how many rows and columns are in each page?

[TDT]

Interesting problem.  I'm pretty good with excel and I like excel challenges, but I don't have an immediate answer.

 

I think if you want to make it look nice you'll have to use a macro.  It will be much more flexible and allow you do filtering, sorting, custom calcs, etc.

 

I *think* at least you'll need a macro to list the sheets names that are available.  Then formulas that refer to those sheet names.  Either way I think there will be macros.

 

Do you know how many rows and columns are in each page?

Only the number of columns is the same, the rows number is variable (and I think this might be a problem too, since I want only rows with values in them, not empty ones...). I've been trying to come up with something for the last 5 hours and I can't get it done, it's above my Excel level

[+primortal Subscriber²]

See if this is any help, http://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html

[TDT]

See if this is any help, http://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html

Nope, or if it is, I don't know how to use it properly. I tried a few scenarios with it but it doesn't do what I need.

Anyway, I tried to do it via macro, recording my steps like this: I made a new sheet (where all the data will be), then went to Sheet1, selected the rows in column A, copied, pasted in the new sheet in column A, went to Sheet2, etc. (see the code bellow). But then I thought it won't work, because the number of rows is not constant I don't know if I can "tell" the macro to select all the rows WITH values AND paste in the first empty row at the bottom in the new sheet...

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+m
'
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=6
    Range("A16:C31").Select
    Selection.Copy
    Sheets("TOTAL").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Range("A16:C24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("TOTAL").Select
    Range("A24").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=15
    Sheets("Sheet3").Select
    Range("A16:C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("TOTAL").Select
    Range("A33").Select
    ActiveSheet.Paste
    Sheets("Sheet4").Select
    Range("A16:C26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("TOTAL").Select
    Range("A36").Select
    ActiveSheet.Paste
    Sheets("Sheet5").Select
    Range("A16:C16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("TOTAL").Select
    ActiveWindow.SmallScroll Down:=15
    Range("A47").Select
    ActiveSheet.Paste
End Sub

[Nick H. Supervisor]

But then I thought it won't work, because the number of rows is not constant I don't know if I can "tell" the macro to select all the rows WITH values AND paste in the first empty row at the bottom in the new sheet...

I had a similar problem recently. I needed a way to tell Excel to stop going through a macro loop when it found a cell that had no value.

As I don't have an example of your spreadsheet and values I can't be sure that this would work, but if it were me I would write a macro that loops through all cells in a column, copying those values to the final sheet until it hits a cell with no value (which is different to 0) then the macro would go through the final sheet and remove any rows in column A (for example) that hold the value 0.

Maybe that doesn't help you in the slightest, but for the moment and with the information available that is what I would look in to trying.

[vsny]

 

Nope, or if it is, I don't know how to use it properly. I tried a few scenarios with it but it doesn't do what I need.

Anyway, I tried to do it via macro, recording my steps like this: I made a new sheet (where all the data will be), then went to Sheet1, selected the rows in column A, copied, pasted in the new sheet in column A, went to Sheet2, etc. (see the code bellow). But then I thought it won't work, because the number of rows is not constant I don't know if I can "tell" the macro to select all the rows WITH values AND paste in the first empty row at the bottom in the new sheet...

 

 

Oh please don't use the macro recorder.  It won't lead to anything that works.

 

The general idea will be:

- Call and loop using the Sheets function to process all the worksheets (ignoring the worksheet you will have the final report - you need a conditional that will ignore the report sheet's name).

- Copy the columns on each sheet to a new colum on the final integrated report

- use end(xlup) to figure out the last row in a column.  Or just copy the whole column

 

This will get you the last row on each worksheet from column A

For i = 1 To Sheets.Count
    r = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
Next i

- paste the rows to new columns

 

This is a nice simple macro to help you learn vba.  Don't use the recorder.  Don't "select" and "paste" functions.