With j = Sheets(MyArr(i)).Range("B9").Value there should be several j's copied to each Sheets(MyArr(i)).Range("B" & Rows.Count).End(xlUp)(2)

The code copies only the last j from "Sales Forecast" to Sheets(MyArr(i)).

Do you see anything in the code that prevents all the j's to be coped to the proper sheet.

I have great confidence in the code since I got help from Claus of this Group, I only changed the value of j from a single sheet cell B9 to the "current" sheet
B9. And each sheet has a different value in B9.

Thanks.
Howard

Option Explicit

Sub ZeroOneDashIandNTester()
Dim c As Range
Dim i As Long
Dim j As String
Dim MyArr As Variant
Dim varOut As Variant
Dim lr As Long
Dim rngB As Range

MyArr = Array("HC-01 (IN)", "HC-02 (IN)", "HC-03 (IN)", _
"HC-04 (IN)", "HC-05 (IN)", "HC-06 (IN)", _
"HC-07 (IN)", "HC-08 (IN)", "HC-09 (IN)", "HC-10 (IN)")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)

With Sheets("Sales Forecast")

j = Sheets(MyArr(i)).Range("B9").Value

lr = .Cells(.Rows.Count, 11).End(xlUp).Row
Set rngB = .Range("B6:B" & lr)

For Each c In rngB
If c = j Then
varOut = c.Offset(, 2).Resize(, 76)
Sheets(MyArr(i)).Range("B" & Rows.Count) _
.End(xlUp)(2).Resize(columnsize:=76) = varOut
End If
Next 'c

End With
Next 'i

Application.ScreenUpdating = True
End Sub

Am Wed, 11 Dec 2013 13:03:20 -0800 (PST) schrieb Howard:

With j = Sheets(MyArr(i)).Range("B9").Value there should be several j's copied to each Sheets(MyArr(i)).Range("B" & Rows.Count).End(xlUp)(2)

The code copies only the last j from "Sales Forecast" to Sheets(MyArr(i)).

Do you see anything in the code that prevents all the j's to be coped to the proper sheet.

I have great confidence in the code since I got help from Claus of this Group, I only changed the value of j from a single sheet cell B9 to the "current" sheet
B9. And each sheet has a different value in B9.

Thanks.
Howard

Option Explicit

Sub ZeroOneDashIandNTester()
Dim c As Range
Dim i As Long
Dim j As String
Dim MyArr As Variant
Dim varOut As Variant
Dim lr As Long
Dim rngB As Range

MyArr = Array("HC-01 (IN)", "HC-02 (IN)", "HC-03 (IN)", _
"HC-04 (IN)", "HC-05 (IN)", "HC-06 (IN)", _
"HC-07 (IN)", "HC-08 (IN)", "HC-09 (IN)", "HC-10 (IN)")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)

With Sheets("Sales Forecast")

j = Sheets(MyArr(i)).Range("B9").Value

lr = .Cells(.Rows.Count, 11).End(xlUp).Row
Set rngB = .Range("B6:B" & lr)

For Each c In rngB
If c = j Then
varOut = c.Offset(, 2).Resize(, 76)
Sheets(MyArr(i)).Range("B" & Rows.Count) _
.End(xlUp)(2).Resize(columnsize:=76) = varOut
End If
Next 'c

End With
Next 'i

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Howard,

Am Wed, 11 Dec 2013 13:03:20 -0800 (PST) schrieb Howard:

With j = Sheets(MyArr(i)).Range("B9").Value there should be several j's copied to each Sheets(MyArr(i)).Range("B" & Rows.Count).End(xlUp)(2)

The code copies only the last j from "Sales Forecast" to Sheets(MyArr(i)).

the found j from Sheet(myarr(i)) is only copied to Sheet(myarr(i))
Do you want that all the found j from each sheet should be copied to
each sheet?


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Wednesday, December 11, 2013 11:07:35 PM UTC-8, Claus Busch wrote:
Hi Howard,



Am Wed, 11 Dec 2013 13:03:20 -0800 (PST) schrieb Howard:



With j = Sheets(MyArr(i)).Range("B9").Value there should be several j's copied to each Sheets(MyArr(i)).Range("B" & Rows.Count).End(xlUp)(2)



The code copies only the last j from "Sales Forecast" to Sheets(MyArr(i)).



the found j from Sheet(myarr(i)) is only copied to Sheet(myarr(i))

Do you want that all the found j from each sheet should be copied to

each sheet?





Regards

Claus B.


I'm not sure I am reading your question, correctly.

I'll try with this:

On the ten sheets in the array, each will have a different value in B9. That value will occur several times in the rngB of Sales Forecasts. So, all those values plus the resize go to that array sheet and when the Next array sheet comes up then there is a different number in B9 and all those matching numbers from rbgB and the resize goes to that sheet being 'processed'. Same with all ten sheets.

The code picks up the last occurrence of each sheets unique B9 number and places it in the correct place, except there are several for each sheet that are bypassed.

Howard

Hi Howard,

Am Thu, 12 Dec 2013 00:26:54 -0800 (PST) schrieb Howard:

On the ten sheets in the array, each will have a different value in B9. That value will occur several times in the rngB of Sales Forecasts. So, all those values plus the resize go to that array sheet and when the Next array sheet comes up then there is a different number in B9 and all those matching numbers from rbgB and the resize goes to that sheet being 'processed'. Same with all ten sheets.

The code picks up the last occurrence of each sheets unique B9 number and places it in the correct place, except there are several for each sheet that are bypassed.

I can't reproduce this.
I have the search string several times in sheet "Sales Forecast" and all
occurences will be copied.
Make sure that the spelling in "Sales Forecast" is the same as in the
other sheets and that there are no trailing or following spaces.
or other not visible characters


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Howard,

Am Thu, 12 Dec 2013 09:37:52 +0100 schrieb Claus Busch:

I can't reproduce this.

what about your range?
You are looking for j in column B
Set rngB = .Range("B6:B" & lr)
but you are checking for lr in column K
lr = .Cells(.Rows.Count, 11).End(xlUp).Row


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Thursday, December 12, 2013 1:33:51 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Thu, 12 Dec 2013 09:37:52 +0100 schrieb Claus Busch:



I can't reproduce this.



what about your range?

You are looking for j in column B

Set rngB = .Range("B6:B" & lr)

but you are checking for lr in column K

lr = .Cells(.Rows.Count, 11).End(xlUp).Row





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

That for sure is a woops, but as luck would have it column K and column B are the same number of rows.

I had checked with a rngB.Select in my trouble shooting and the correct range of column B was selected.

I'll give stuff another look over like you suggest.

Howard