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

Consolidated different worksheet

• QUESTION: I am getting a problem Consolidated different worksheet in console file but I am face a problem, I will tell U the step which is followed 1) I am receiving a 6 to 7 different file every day from different site 2) Then i have to console the file in 1worksheet but in worksheet i have to do copy %26 paste in sheet1 %26 sheet2 3) I will show you the code but I am getting an error please suggest me what should I do, Below I am pasting the code Sub Console() ' ' Console Macro ' Macro recorded 9/2/2010 by Sainath_S ' ' Keyboard Shortcut: Ctrl+Shift+S ' Range("A1").Select Workbooks.Open Filename:= _ "C:Documents and SettingsSainath_SDesktopCollectIndiaPrecollect-Columbia - Final.xls" Range("A1:W10").Select Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste ActiveSheet.Next.Select Windows("IndiaPrecollect-Columbia - Final.xls").Activate ActiveSheet.Next.Select Range("A1:P5").Select Application.CutCopyMode = False Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste ActiveSheet.Previous.Select Selection.End(xlDown).Select Range("A11").Select Windows("IndiaPrecollect-Columbia - Final.xls").Activate ActiveWorkbook.Close Workbooks.Open Filename:= _ "C:Documents and SettingsSainath_SDesktopCollectIndiaPrecollect-EarthCityAndDetroit - Final.xls" Range("A1:W108").Select Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A119").Select ActiveSheet.Next.Select Range("A6").Select Windows("IndiaPrecollect-EarthCityAndDetroit - Final.xls").Activate ActiveSheet.Next.Select Range("A1:P42").Select Application.CutCopyMode = False Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A48").Select Windows("IndiaPrecollect-EarthCityAndDetroit - Final.xls").Activate ActiveWorkbook.Close Workbooks.Open Filename:= _ "C:Documents and SettingsSainath_SDesktopCollectIndiaPrecollect-MendotaHeights - Final.xls" Range("A1:W28").Select Selection.Copy Windows("Console.xls").Activate ActiveSheet.Previous.Select ActiveSheet.Paste Selection.End(xlDown).Select Range("A147").Select ActiveSheet.Next.Select Windows("IndiaPrecollect-MendotaHeights - Final.xls").Activate ActiveSheet.Next.Select Range("A1:P13").Select Application.CutCopyMode = False Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A61").Select Windows("IndiaPrecollect-MendotaHeights - Final.xls").Activate ActiveWorkbook.Close Workbooks.Open Filename:= _ "C:Documents and SettingsSainath_SDesktopCollectIndiaPrecollect-MendotaHeights - Final.xls" Windows("Console.xls").Activate ActiveSheet.Previous.Select ActiveSheet.Next.Select ActiveSheet.Previous.Select Windows("IndiaPrecollect-MendotaHeights - Final.xls").Activate ActiveWorkbook.Close Workbooks.Open Filename:= _ "C:Documents and SettingsSainath_SDesktopCollectIndiaPrecollect-Sacramento - Final.xls" Range("A1:W270").Select Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A417").Select ActiveSheet.Next.Select Windows("IndiaPrecollect-Sacramento - Final.xls").Activate ActiveSheet.Next.Select Range("A1:P122").Select Application.CutCopyMode = False Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A183").Select Windows("IndiaPrecollect-Sacramento - Final.xls").Activate Application.CutCopyMode = False ActiveWorkbook.Close ActiveSheet.Previous.Select Workbooks.Open Filename:= _ "C:Documents and SettingsSainath_SDesktopCollectIndiaPrecollect-Tampa - Final.xls" Range("A1:W61").Select Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A478").Select ActiveSheet.Next.Select Windows("IndiaPrecollect-Tampa - Final.xls").Activate ActiveSheet.Next.Select Range("A1:P22").Select Application.CutCopyMode = False Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A205").Select ActiveSheet.Previous.Select Windows("IndiaPrecollect-Tampa - Final.xls").Activate ActiveWorkbook.Close Workbooks.Open Filename:= _ "C:Documents and SettingsSainath_SDesktopCollectIndiaPrecollect-Totowa - Final.xls" Range("A1:W367").Select Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A845").Select Windows("IndiaPrecollect-Totowa - Final.xls").Activate ActiveSheet.Next.Select Range("A1:P154").Select Application.CutCopyMode = False Selection.Copy Windows("Console.xls").Activate ActiveSheet.Next.Select ActiveSheet.Paste Selection.End(xlDown).Select Range("A359").Select ActiveSheet.Previous.Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Range("A2").Select ActiveWindow.FreezePanes = True Selection.End(xlDown).Select Range("A845").Select Windows("IndiaPrecollect-Totowa - Final.xls").Activate ActiveWorkbook.Close Workbooks.Open Filename:= _ "C:Documents and SettingsSainath_SDesktopCollectIndiaPrecollect-Medicare - Final.xls" Range("A1:X438").Select Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlDown).Select Range("A1283").Select ActiveSheet.Next.Select Windows("IndiaPrecollect-Medicare - Final.xls").Activate ActiveSheet.Next.Select Range("A1:O135").Select Application.CutCopyMode = False Selection.Copy Windows("Console.xls").Activate ActiveSheet.Paste Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Application.CutCopyMode = False Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="ControlNo" Range("A6").Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Previous.Select Range("A1282").Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.AutoFilter Range("A1").Select Selection.AutoFilter Field:=1, Criteria1:="CID" Range("A11").Select Range(Selection, Selection.End(xlDown)).Select Rows("11:11").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveSheet.ShowAllData Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.RowHeight = 12.75 Range("A1").Select ActiveSheet.Next.Select Rows("6:6").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveSheet.ShowAllData Range("A2").Select Sheets("Sheet2").Name = "Assign" ActiveSheet.Previous.Select Range("A1").Select Sheets("Sheet1").Name = "All" ActiveSheet.Next.Select ActiveSheet.Next.Select ActiveWindow.SelectedSheets.Delete Columns("B:B").Select Selection.Insert Shift:=xlToRight Selection.Insert Shift:=xlToRight Range("B1").Select ActiveSheet.Previous.Select Selection.Copy ActiveSheet.Next.Select ActiveSheet.Paste Application.CutCopyMode = False Range("C1").Select ActiveSheet.Previous.Select Range("F1").Select Selection.Copy ActiveSheet.Next.Select ActiveSheet.Paste Range("B2").Select ActiveSheet.Previous.Select Range("D1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Columns("A:A").Select Application.CutCopyMode = False Selection.Cut Range("C1").Select Columns("C:C").Select Selection.Insert Shift:=xlToRight Range("A1").Select ActiveSheet.Next.Select Range("B2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],All!C[-1]:C,2,0)" Range("C2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],All!C[-2]:C[3],6,0)" Range("A2").Select Selection.End(xlDown).Select Range("B487:C487").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown Range("A487").Select Selection.End(xlUp).Select Columns("B:C").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  :=False, Transpose:=False Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlToLeft).Select Cells.Select Cells.EntireColumn.AutoFit Range("B2").Select Application.CutCopyMode = False ActiveWorkbook.Save End Sub ANSWER: What error do you get exactly and on which line in the code? ---------- FOLLOW-UP ---------- QUESTION: Windows("IndiaPrecollect-Columbia - Final.xls").Activate The above line onwards i am getting the error

• Answer:

  Check if a file called IndiaPrecollect-Columbia - Final.xls is open inat the moment the code reaches that line. If it seems to be, check if the filename is EXACTLY the same as what is between the quotes on the line you show above. Maybe there is a space too many or too less.