Need help compiling data from other wooksheets

[simondrew]

Need help.

I have 5 sheets in 1 wookbook with about 15 columns of varying rows of data. What I need to do is create a macro which collates all the rows of data, which with their own heading, onto 1 sheet so that it can be printed and distributed.

The problem is of course is that normal copy and paste won't work because the rows may vary and the macro need to take that into consideration. I guess probably an 'If not or' type command that would copy and paste the row so long as it had values then move onto the next sheet and continue from the last one.

Thanks in advance.
Simon.

 

[agihcam]

hi -

Welcome to the board. you can try this code.

Sub consolidate()
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("ConsolidatedData").Delete
Sheets.Add before:=Sheets(1)
Sheets(1).Name = "ConsolidatedData"
Dim ii As Long
For ii = 2 To Sheets.Count
   Sheets(ii).Range("A1:AA1000").Copy  'change the range to copy
     With Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .PasteSpecial xlValues
    End With
Next ii
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a1").Activate
End Sub

 

[simondrew]

Thanks

Thanks for that. Worked like a charm!!!

Simon.

 

[Goods]

Great code...works perfect and it's very fast

 

[Oli.Harwood]

How about if we had different files that we wished to consolidate?

I've got a folder which contains a number of xls files.

What I want to do is for the macro to run through all the files in the folder (which I can get it do) open them up, copy any data that is there (minus the header row) and then paste it to the bottom of the consolidated workbook.

Does anyone have a good idea of how to tackle that?

I can get it do most of it, the problem I have is how to tell the macro to paste the data to the next free row?

EDIT: Actually managed to get it to work now.

 

[BillyTopez]

consolidating data from different files

Does anyone have an answer to Oli.Harwood's problem? I am also looking for a macro that goes through all the files in a folder (the files all have the same template) and copies the data from them minus the header row into a separate consolidate workbook.

 

[tinkerbell737]

suppress blank rows?

I love this macro - thank you for sharing!

Any chance that there is an easy modification to suppress or delete the blank rows? My work-around right now is to sort and then delete the blanks, which isn't a big deal, but if there is an easier way...

thanks again

 

[help_questions]

. n

 

[TiffanyG]

I have been trying to get a code like this but I have multiple files (for different districts - 6 files) that have the same sheet names that I need to consolidate into a corp file. I need the code to open the file and copy the sheet, without the header which would be A1-A6, into the next empty row of the consolidated file (first empty row would be 7 - becasue of header)so the end result is all the districts consolidated into a corp.

Is this possible? I have been opening each file, copying the data, and pasting special into the corp file. I have 4 sheets I do this to and it takes forever.

TiffanyG

 

[jindon]

Alter to suite

Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C:\test\"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn <> ""
     Set ws = Workbooks.Open(myDir & fn).Sheets(1)
     ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy
     ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
     Workbooks(fn).Close False
     fn = Dir
Loop
End Sub