Code copies ONLY last j from sheet

H

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
 
C

Claus Busch

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
Click to expand...


Regards
Claus B.
 
C

Claus Busch

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)).
Click to expand...

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.
 
H

Howard

Hi Howard,



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






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.
Click to expand...

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 arraysheet 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 thatare bypassed.

Howard
 
C

Claus Busch

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.
Click to expand...

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.
 
C

Claus Busch

Hi Howard,

Am Thu, 12 Dec 2013 09:37:52 +0100 schrieb Claus Busch:
I can't reproduce this.
Click to expand...

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.
 
H

Howard

Hi Howard,



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






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
Click to expand...

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
 
H

Howard

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
Click to expand...



I looked it over and the only thing I saw was the
varOut = c.Offset(, 2).Resize(, 76)
Resize did not have a row number. I was under the impression at least a 1 was required with resize for the row, unlike offset where the coma is sufficient. I put the 1 in and no change.

I did the =B9='Sales Forecast'!B16 test and it returns true for each sheet against one of the values per sheet in rngB.

And I tried a different copy paste line but the results are still the same.

The green tabs are the only ones of concern and there is only data to go to
HC-01, HC-03, HC-04 & HC-05.

I left the copied data as the code produces in each of those sheets. If you run the code twice it copies over the existing posted data, does not offset.

Here is my copy of the workbook, take a look at you convenience, if you don't mind.

https://www.dropbox.com/s/dafrbm0her6xvfk/Copy ARRAY TEST Drop Box.xlsm

thanks, Howard.
 
C

Claus Busch

Hi Howard,

Am Thu, 12 Dec 2013 02:49:54 -0800 (PST) schrieb Howard:
https://www.dropbox.com/s/dafrbm0her6xvfk/Copy ARRAY TEST Drop Box.xlsm
Click to expand...

in sheet "Sales forecast" there is no customer. So column B in the other
sheets is not filled and Range("B" & Rows.Count).End(xlUp)(2) is always
the same. Your rows will be overwritten.Therefore you only see the last
occurence

Try:
Sub ZeroOneDashIandNTester2()
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)
lr = Sheets(MyArr(i)).Cells(Rows.Count, 3).End(xlUp).Row
Sheets(MyArr(i)).Range("B" & lr + 1). _
Resize(columnsize:=76) = varOut
End If
Next 'c

End With
Next 'i

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
H

Howard

Hi Howard,



Am Thu, 12 Dec 2013 02:49:54 -0800 (PST) schrieb Howard:






in sheet "Sales forecast" there is no customer. So column B in the other

sheets is not filled and Range("B" & Rows.Count).End(xlUp)(2) is always

the same. Your rows will be overwritten.Therefore you only see the last

occurence



Try:

Sub ZeroOneDashIandNTester2()

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)

lr = Sheets(MyArr(i)).Cells(Rows.Count, 3).End(xlUp).Row

Sheets(MyArr(i)).Range("B" & lr + 1). _

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
Click to expand...


Works a treat! I would have never found that.

Thanks for taking the time to look at the workbook.

Regards,
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tried and true copy code lines fail me here 7
Convert list to UPPER, lower & Proper cases. 21
Arrays and scattered cell values or another way? 16
A simple if <> nn then Msgbox "No Match" 5
Open four Wbooks, copy four columns from each to Master Wbook 16
Code copies twice...? 13
Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
Modify working code to expand the output. 5

Top