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 he

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

Copy range from multiple workbooks and include workbook name
 

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" wrote in message
...
Reposted in Programming Newsgroup.

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

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