Copy from multiple workbooks and display the original worksheet na
 

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

Posted to wrong forum. Reposted in PROGRAMMING.

"Barb Reinhardt" wrote:

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