Consolidating reports

[pliant]

I have reports done in Excel sent to be by various locations every month. I'd like to combine all these reports into one large report. Is there an easy way to automate this so that I can just click a button each month for this to happen instead of cut and pasting each one into the large report?

 

[pliant]

Would someone be able to explain to me what this is doing?

<code>
If Counter = 1 Then
Range("A1:K" & Range("A65536").End(xlUp).Row).Copy _
Destination:=Workbooks(Rollup_File_Name). _
Sheets(1).Range("A1")
Else
Range("A2:K" & Range("A65536").End(xlUp).Row).Copy _
Destination:=Workbooks(Rollup_File_Name). _
Sheets(1).Range("A65536").End(xlUp).Offset(1, 0)
End If
</code>

 

[Barrie Davidson]

If the value of the variable "Counter" equals 1, then copy the range A1:K "last row" to A1. If "Counter" doesn't equal 1, copy the same range to the first empty row in Sheet #1 in the workbook defined by the variable "Rollup_File_Name".

Does this answer your question?

 

[pliant]

so these two statements find the last row with a value in it?


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

Range("A65536").End(xlUp).Offset(1, 0)

 

[Barrie Davidson]

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

This finds the last row with data in it.

Range("A65536").End(xlUp).Offset(1, 0)

This finds the row below the last row with data in it.

 

[pliant]

ok...i guess it consider functions in the cell data as well...is there a way to get only the values...for example B7:B50 all contain a function but only B7:B10 contain data...i only want it to Copy row B7:B10 with data in them but its copying from B7:B50

 

[Barrie Davidson]

ok...i guess it consider functions in the cell data as well...is there a way to get only the values...for example B7:B50 all contain a function but only B7:B10 contain data...i only want it to Copy row B7:B10 with data in them but its copying from B7:B50

Alright, two questions for you:

1. In the original data range (A1:K & last row), what column contains the formula that you want to filter out rows with no value in that column? Is it column B?

2. Does the original data range (A1:K & last row) contain a header (i.e., is the first row a description of data in that column)?

 

[LukeJ]

Hi the error im getting is Error 424 and its sayin Object required. then it highlights this line when i goto debug Select Case File_Save_Name.Value