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

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

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

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