Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
On Thursday, December 12, 2013 1:47:03 AM UTC-8, Howard wrote:
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 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/dafrbm0her...rop%20Box.xlsm thanks, Howard. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
Hi Howard,
Am Thu, 12 Dec 2013 02:49:54 -0800 (PST) schrieb Howard: https://www.dropbox.com/s/dafrbm0her...rop%20Box.xlsm 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code copies ONLY last j from sheet
On Thursday, December 12, 2013 3:35:12 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Thu, 12 Dec 2013 02:49:54 -0800 (PST) schrieb Howard: https://www.dropbox.com/s/dafrbm0her...rop%20Box.xlsm 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 Works a treat! I would have never found that. Thanks for taking the time to look at the workbook. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code copies twice...? | Excel Programming | |||
Code copies between key word is in reverse order | Excel Programming | |||
Code For Copy Copies Twice | Excel Programming | |||
Can cells be copies automatically to Sheet 2 and Sheet 3 | New Users to Excel | |||
Trying to eliminate multiple copies of the SAME code within a UserForm | Excel Programming |