How to copy each row from a worksheet into new workbooks?

Vba- copy

• QUESTION: Any help you could offer would be greatly appreciated! I need to copy a worksheet from one workbook to another and override the destination workbook name. I'm using the following code: Sub GetFile() ' This macro will import a file into this workbook ' Copyright 1999 www.MrExcel.com Sheets("Sheet1").Select PathName = Range("D3").Value Filename = Range("D4").Value TabName = Range("D5").Value ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) Windows(Filename).Activate ActiveWorkbook.Close SaveChanges:=False Windows(ControlFile).Activate Sheets("Sheet1").Select Range("D8").Select ActiveCell.Value = "Completed" Range("D9").Select End Sub This works, the problem is that every time I run the macro it adds another worksheet. IE first runs adds "copied" next run adds "copied(1)" next run adds "copied(2)". I need to overwrite "copied" every time. ANSWER: Steven, I will assume there is no more than one sheet with the root name "copied" in the activeworkbook when you run the code. This will delete any such sheet before doing the copy Sub GetFile() ' This macro will import a file into this workbook ' Copyright 1999 www.MrExcel.com Sheets("Sheet1").Select PathName = Range("D3").Value Filename = Range("D4").Value TabName = Range("D5").Value set bk = Activeworkbook on Error Resume Next Application.DisplayAlerts = False bk.Worksheets("TabName").Delete Application.DisplayAlerts = True On Error goto 0 ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) Windows(Filename).Activate ActiveWorkbook.Close SaveChanges:=False Windows(ControlFile).Activate Sheets("Sheet1").Select Range("D8").Select ActiveCell.Value = "Completed" Range("D9").Select End Sub Test it on a copy of your workbook since it is deleting a sheet If that doesn't do what you want, post a followup with what is happening that you don't want. ---------- FOLLOW-UP ---------- QUESTION: Wow, Your solution did not seem to work, however even if it had worked there is a side effect I did not see coming. It seems that by deleting the sheet first it breaks any references to the "names" that I had defined on other sheets. The names do carry over with the copy however their scope is now limited only to that sheet. Just one idea- Is it possible to copy a "named" set of data in one file to a "named" set on the destination file? Steve QUESTION: Any help you could offer would be greatly appreciated! I need to copy a worksheet from one workbook to another and override the destination workbook name. I'm using the following code: Sub GetFile() ' This macro will import a file into this workbook ' Copyright 1999 www.MrExcel.com Sheets("Sheet1").Select PathName = Range("D3").Value Filename = Range("D4").Value TabName = Range("D5").Value ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) Windows(Filename).Activate ActiveWorkbook.Close SaveChanges:=False Windows(ControlFile).Activate Sheets("Sheet1").Select Range("D8").Select ActiveCell.Value = "Completed" Range("D9").Select End Sub This works, the problem is that every time I run the macro it adds another worksheet. IE first runs adds "copied" next run adds "copied(1)" next run adds "copied(2)". I need to overwrite "copied" every time. ANSWER: Sub GetFile() ' This macro will import a file into this workbook ' Copyright 1999 www.MrExcel.com Sheets("Sheet1").Select PathName = Range("D3").Value Filename = Range("D4").Value TabName = Range("D5").Value ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName On Error Resume Next ' End Sub ---------- FOLLOW-UP ---------- QUESTION: Wow, Your solution worked, however there is a side effect I did not see coming. It seems that by deleting the sheet first it breaks any references to the "names" that I had defined on other sheets. The names do carry over with the copy however their scope is now limited only to that sheet. Just one idea- Is it possible to copy a "named" set of data in one file to a "named" set on the destination file? Steve

• Answer:

  Steve, there was a typo in the code snippet I provided on Error Resume Next Application.DisplayAlerts = False bk.Worksheets("TabName").Delete Application.DisplayAlerts = True On Error goto 0 should be on Error Resume Next Application.DisplayAlerts = False bk.Worksheets(TabName).Delete Application.DisplayAlerts = True On Error goto 0 Since tabname is a variable - sometime hard to keep it all straight when working with someone elses ideas/code. Sorry for the confusion. As far as copying names, What you see when you do it manually is what you get. I suppose the macro could always be designed to read the existing names and redefine them when the sheet is copied to restore them. To do anything would require specific knowledge of what needs to be done and how your names are set up. > Just one idea- Is it possible to copy a "named" set of data in one file to a "named" set on the destination file? sure set r1 = workbooks("ABC.xls").Names("Myname1").ReferstoRange set r2 = workbooks("EFG.xls").Names("SomeotherName").ReferstoRange r1.copy r2(1) You mwy want to consider reusing "copied" by copying CELLS, not the SHEET (this is untested) Sub GetFile() ' This macro will import a file into this workbook ' Copyright 1999 www.MrExcel.com Sheets("Sheet1").Select PathName = Range("D3").Value Filename = Range("D4").Value TabName = Range("D5").Value ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName Sheets(TabName).Cells.Copy ' End Sub