try this
http://akoul.blogspot.com/search/label/Merge%20data%20from%20multiple%20sheet%20from%20multiple%20workbook%20to%20a%20workbook
On Sun, Jul 3, 2011 at 11:33 AM, Nasim <nbeiz...@gmail.com> wrote:
> 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
>
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com <http://akoul.blogspot.com/>
http://akoul.posterous.com/
*akoul*.wordpress.com <http://akoul.wordpress.com/>
My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
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
