Copy range from multiple workbooks and include workbook name

Reposted in Programming Newsgroup.

I'm following Ron de Bruin's example (his Example 1) shown here:

http://www.rondebruin.nl/copy3.htm#Range

I want to include the name of the worksheet in column B, and I'm using this
code (lifted from Ron) but I can't figure out what's wrong.

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
lrow = LastRow(mybook.Sheets(1))
Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow)
'Copy from A2:IV? (till the last row with data on your sheet)
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

basebook.Worksheets(1).Cells(rnum, "B").Value = mybook.Name
' This will add the workbook name in column B if you want

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only the
values

' With sourceRange
' Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
' Resize(.Rows.Count, .Columns.Count)
' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
FNames = Dir()
Loop

Thanks,
Barb Reinhardt

Hi Barb

This will overwrite the workbook name because you copy A2:IV" & lrow
sourceRange.Copy destrange

Use the code line after the line above.
It will overwrite the value that is in that cell, Is that OK ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> Reposted in Programming Newsgroup.
>
> I'm following Ron de Bruin's example (his Example 1) shown here:
>
> http://www.rondebruin.nl/copy3.htm#Range
>
> I want to include the name of the worksheet in column B, and I'm using this
> code (lifted from Ron) but I can't figure out what's wrong.
>
> Do While FNames <> ""
> Set mybook = Workbooks.Open(FNames)
> lrow = LastRow(mybook.Sheets(1))
> Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow)
> 'Copy from A2:IV? (till the last row with data on your sheet)
> SourceRcount = sourceRange.Rows.Count
> Set destrange = basebook.Worksheets(1).Cells(rnum, "A")
>
> basebook.Worksheets(1).Cells(rnum, "B").Value = mybook.Name
> ' This will add the workbook name in column B if you want
>
> sourceRange.Copy destrange
> ' Instead of this line you can use the code below to copy only the
> values
>
> ' With sourceRange
> ' Set destrange = basebook.Worksheets(1).Cells(rnum,
> "A"). _
> ' Resize(.Rows.Count, .Columns.Count)
> ' End With
> ' destrange.Value = sourceRange.Value
>
> mybook.Close False
> rnum = rnum + SourceRcount
> FNames = Dir()
> Loop
>
> Thanks,
> Barb Reinhardt
>