Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should display tabs on multiple rows | Excel Worksheet Functions | |||
Excel does not display multiple workbooks on the windows taskbar | Excel Discussion (Misc queries) | |||
Excel does not display workbooks open | Setting up and Configuration of Excel |