Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Loop, 2 workbooks
Hello all,
I have workbook A and workbook B. Both are the same, 130 sheets each. I need to copy colum B from workbook B (which has changes) to A. I'm looking for a loop that will start off on sheet 2 of both workbooks then go to the next sheet (3) etc, to the end of both workbooks. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Loop, 2 workbooks
Sub Copy_Col_B()
Set Bk1 = Workbooks("A.xls") Set Bk2 = Workbooks("B.xls") For Sht = 2 To Bk1.Sheets.Count Bk1.Sheets(Sht).Columns("B").Copy _ Bk2.Sheets(Sht).Columns("B") Next Sht End Sub "pgarcia" wrote: Hello all, I have workbook A and workbook B. Both are the same, 130 sheets each. I need to copy colum B from workbook B (which has changes) to A. I'm looking for a loop that will start off on sheet 2 of both workbooks then go to the next sheet (3) etc, to the end of both workbooks. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Loop, 2 workbooks
Thanks, but it did not seem to work. And, I change it a bit, but sines it did
not work, it didn't. Here is what I'm trying to do. Question, I see where Bk1 changes to the next sheet, but when does Bk2 change to the next sheet? Sub FuelCopy() Set Bk1 = Workbooks("CMS Fuel Update20080801.xls") Set Bk2 = Workbooks("CMS Fuel Update20080905.xls") For Sht = 2 To Bk1.Sheets.Count Bk1.Sheets(Sht).Columns("B").Copy _ Bk2.Sheets(Sht).Columns("B") Windows("CMS Fuel Update20080905.xls").Activate Range("B1").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Cells.Replace What:="[CMS Fuel Update20080801.xls]", Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next Sht End Sub "Joel" wrote: Sub Copy_Col_B() Set Bk1 = Workbooks("A.xls") Set Bk2 = Workbooks("B.xls") For Sht = 2 To Bk1.Sheets.Count Bk1.Sheets(Sht).Columns("B").Copy _ Bk2.Sheets(Sht).Columns("B") Next Sht End Sub "pgarcia" wrote: Hello all, I have workbook A and workbook B. Both are the same, 130 sheets each. I need to copy colum B from workbook B (which has changes) to A. I'm looking for a loop that will start off on sheet 2 of both workbooks then go to the next sheet (3) etc, to the end of both workbooks. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Loop, 2 workbooks
You don't need to activate and window when using the copy method like I did.
You also didn't reference the sht with your new REPLACE mwthod statement. The code depends on the worksheets being in the same order in the two workbooks. I'm referecing each worksheet by the index number 1 to 130 with sheets(Sht). I added a method 2 below which I think is a little bit more robust. method 1 Sub FuelCopy() Set Bk1 = Workbooks("CMS Fuel Update20080801.xls") Set Bk2 = Workbooks("CMS Fuel Update20080905.xls") For Sht = 2 To Bk1.Sheets.Count Bk1.Sheets(Sht).Columns("B").Copy _ Bk2.Sheets(Sht).Columns("B") Bk2.sheets(Sht).Cells. _ Replace What:="[CMS Fuel Update20080801.xls]", _ Replacement:="", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False Next Sht End Sub method 2 Sub FuelCopy() Set Bk1 = Workbooks("CMS Fuel Update20080801.xls") Set Bk2 = Workbooks("CMS Fuel Update20080905.xls") For Sht = 2 To Bk1.Sheets.Count ShtName = BK1.Sheets(Sht).Name Bk1.Sheets(ShtName).Columns("B").Copy _ Bk2.Sheets(ShtName).Columns("B") Bk2.sheets(ShtName).Cells. _ Replace What:="[CMS Fuel Update20080801.xls]", _ Replacement:="", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False Next Sht End Sub "pgarcia" wrote: Thanks, but it did not seem to work. And, I change it a bit, but sines it did not work, it didn't. Here is what I'm trying to do. Question, I see where Bk1 changes to the next sheet, but when does Bk2 change to the next sheet? Sub FuelCopy() Set Bk1 = Workbooks("CMS Fuel Update20080801.xls") Set Bk2 = Workbooks("CMS Fuel Update20080905.xls") For Sht = 2 To Bk1.Sheets.Count Bk1.Sheets(Sht).Columns("B").Copy _ Bk2.Sheets(Sht).Columns("B") Windows("CMS Fuel Update20080905.xls").Activate Range("B1").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Cells.Replace What:="[CMS Fuel Update20080801.xls]", Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next Sht End Sub "Joel" wrote: Sub Copy_Col_B() Set Bk1 = Workbooks("A.xls") Set Bk2 = Workbooks("B.xls") For Sht = 2 To Bk1.Sheets.Count Bk1.Sheets(Sht).Columns("B").Copy _ Bk2.Sheets(Sht).Columns("B") Next Sht End Sub "pgarcia" wrote: Hello all, I have workbook A and workbook B. Both are the same, 130 sheets each. I need to copy colum B from workbook B (which has changes) to A. I'm looking for a loop that will start off on sheet 2 of both workbooks then go to the next sheet (3) etc, to the end of both workbooks. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New Loop, 2 workbooks | Excel Programming | |||
Help:loop on two workbooks | Excel Programming | |||
Loop with two workbooks | Excel Programming | |||
loop through workbooks | Excel Programming | |||
loop through workbooks | Excel Programming |