Hi Ashish, First of all congrats! You are a big help to all of us :)
Also, Thanks a lot for your help. you. I tweaked your code a bit and got it to work the way I needed. now I have another problem: I am combining files from different location into one sheet (a new workbook). The list of files to open is in the wb with codes. the problem is i can not get the code to find the last row on the combined sheet (to add info to the end of used range). here is my code: Application.SheetsInNewWorkbook = 1 'setting # of sheets to be in the new wb Set wbDest = Workbooks.Add strSummaryPath = ThisWorkbook.Path ' to get the path so new sheet can be saved here strSummaryName = "Wk1 Summary.xlsx" With wbDest .Sheets(1).Range("A1") = "Type" .Sheets(1).Range("B1") = "Name" .SaveAs strSummaryPath & "\" & strSummaryName, FileFormat:=51 End With ThisWorkbook.Sheets("collect files").Activate '<< here is the list of files to open LastRowT = ThisWorkbook.Sheets("collect files").Cells(Rows.Count, 1).End(xlUp).Row ' works fine For i = 2 To LastRowT 'wbDest.Activate 'MsgBox wbDest.Name & " index is: " & wbDest.Sheets("sheet1").Index ' just checking LastRowDest = wbDest.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row '<<<<<<<<<<<<<<<<<<<<Does not work. keeps shoing 0 'Rest of codes work fine except that is always copies on top of eachother becuase LastRowDest is always 0 strPath = ThisWorkbook.Sheets("collect files").Range("T" & i) & "Managers Folder\" strFile = ThisWorkbook.Sheets("collect files").Range("U" & i) Set wbSource = Workbooks.Open(strPath & strFile) 'wbSource.Sheets(2).Activate LastRowSource = wbSource.Sheets(2).Cells(Rows.Count, 2).End(xlUp).Row wbSource.Sheets(2).Range("A2:V" & LastRowSource).Select Selection.Copy wbDest.Sheets(1).Activate With wbDest.Sheets(1).Range("A" & LastRowDest + 1) .PasteSpecial Paste:=8 .PasteSpecial Paste:=xlAll End With Application.CutCopyMode = False wbSource.Sheets(2).Activate wbSource.Sheets(2).Rows("1:1").Copy wbDest.Sheets(1).Activate With wbDest.Sheets(1).Range("A1") .PasteSpecial Paste:=8 ' Paste:=8 will copy the columnwidth in Excel 2000 and higher .PasteSpecial Paste:=xlAll End With Application.CutCopyMode = False wbSource.Close savechanges:=False Next i wbDest.Save wbSource = Nothing wbDest = Nothing End Sub I appreciate if you could show me what i am doing wrong. Thanks a lot for your help in advance. Nasim -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel