Copying to new workbook

[Massy]

Hi

I have the following code which should copy all values
from one workbook to the main workbook.

The workbook has 25 Sheets (1 menu and the rest relate to
months) I get an updated wb each week which i want to
merge into the main document(Prepay_Main.xls)

I have a couple of problems with this.

Firstly how do i set the variable currentwb to the
activebooks name?

Also how would i check to see if there are any values in a
worksheet before i copy? There will always be a title row
and formulas in some cells.

LEt me know if you want more info


Sub UpdateMain()

Dim x As Integer
Dim lr As Long
Dim cr As Long
Dim currentwb As Workbook

currentwb = Workbook.Name 'set to current wb (not working)
Set Main = Workbooks.Open("S:\Kingston\fa\overseas
payments\long haul\2004
\austravel\prepayments\Prepay_Main.xls")
For x = 2 To 26
Workbooks(current).Activate 'activate current wb (not
working)
Worksheets(x).Activate
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:I" & lr).Copy
Workbooks("Prepay_Main.xls").Activate
Worksheets(x).Select
cr = Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Paste
Next x

End Sub

 

[mark]

Firstly how do i set the variable currentwb to the

activebooks name?

Dim currentwb As Workbook
currentwb = Workbook.Name 'set to current wb (not working)

Hi.

You've dimensioned your variable currentwb as a workbook,
not as a string, so you need to assign it the workbook,
not the workbook name, which is a text string.

use

set currentwb = ActiveWorkbook


As for the other question, since you have some formulas,
it would probably help to have you post a little bit more
about the characteristics of what a sheet with no data
looks like... is it a sheet that only has a header in
column A? but nothing else in column A? etc.