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
