For Each loop Range of Cells, Cell Selection, VBA

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Code:
wb2.ActivateSheets("Invoice").Activate
For Each cell In Worksheets("Invoice").Range("A37,A45,A53")


If cell.Value = "OK" Then


wb2.Activate
Sheets("Invoice").Activate
Range("B3").Copy
wb1.Activate
Sheets("General Ledger").Activate
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("OK").Activate
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
wb2.Activate
Sheets("Invoice").Activate
ActiveCell.Copy
wb1.Activate
Sheets("General Ledger").Activate
Range("A1").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("OK").Activate
Range("A1").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
wb2.Activate
Sheets("Invoice").Activate
ActiveCell.Offset(0, 4).Copy
wb1.Activate
Sheets("General Ledger").Activate
Range("A1").End(xlDown).Offset(0, 58).Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("OK").Activate
Range("A1").End(xlDown).Offset(0, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues

This is part of a loop that is intended to loop for the three cells specified in line

Code:
For Each cell In Worksheets("Invoice").Range("A37,A45,A53")

I am concerned that after the initial part of the script:

Code:
wb2.ActivateSheets("Invoice").Activate
Range("B3").Copy
wb1.Activate
Sheets("General Ledger").Activate
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("OK").Activate
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

the activecell will be different from the cell that is the subject of the loop (A37,A45,or A53) which I would like to use as the basis for the next several steps in the loop.

There are two other If - Then lines with basically the same script in between

How do I select the correct cell (A37, A45, or A53) as the activecell on "invoice" sheet if I have already selected a cell earlier in the script? Ideas?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,214,545
Messages
6,120,128
Members
448,947
Latest member
test111

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top