G
Guest
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
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