Tried and true copy code lines fail me here

[Howard]

Do I have something up-stream of the copy lines (both which error out) to make them fail?

Code is in a standard module
I have seven sheets commented out as I test the code.

Thanks,
Howard

Option Explicit

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

MyArr = Array("01-IN", "02-IN", "03-IN") ', "04-IN", "05-IN", "06-IN", "07-IN", "08-IN", "09-IN", "10-IN")
Application.ScreenUpdating = False

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

With Sheets("Sales Forecast")
j = Range("B3").Value
lr = Cells(Rows.Count, 11).End(xlUp).Row
Set rngB = Range("B13:B" & lr)

For Each c In rngB
If c = j Then

c.Offset(, 2).Resize(1, 76).Copy
Sheets(i).Range("B" & Rows.Count) _
.End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

'Sheets(i).Range("B" & Rows.Count) _
.End(xlUp)(2) = c.Offset(, 2).Resize(1, 76)

End If

Next 'c

End With
Next 'i

Application.ScreenUpdating = True
End Sub

 

[Howard]

Do I have something up-stream of the copy lines (both which error out) to make them fail?



Code is in a standard module

I have seven sheets commented out as I test the code.



Thanks,

Howard



Option Explicit



Sub ZeroOneDashIandN()

Dim c As Range

Dim i As Long

Dim j As String

Dim MyArr As Variant

Dim lr As Long

Dim rngB As Range



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

Application.ScreenUpdating = False



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



With Sheets("Sales Forecast")

j = Range("B3").Value

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

Set rngB = Range("B13:B" & lr)



For Each c In rngB

If c = j Then



c.Offset(, 2).Resize(1, 76).Copy

Sheets(i).Range("B" & Rows.Count) _

.End(xlUp)(2).PasteSpecial Paste:=xlPasteValues



'Sheets(i).Range("B" & Rows.Count) _

.End(xlUp)(2) = c.Offset(, 2).Resize(1, 76)



End If



Next 'c



End With

Next 'i



Application.ScreenUpdating = True

End Sub

Just to add, if I can, j value is actually on sheet("01-IN") cell B9. I tried this line in the code where it works on other examples but errors here.

j = Sheets("01-IN").Range("B9").Value

I had placed it just above the For i = LBound(MyArr) To UBound(MyArr) line.
So now I have a formula in Sales Forecast range(B3) ='01-IN'!B9


Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 10 Dec 2013 11:07:04 -0800 (PST) schrieb Howard:

j = Sheets("01-IN").Range("B9").Value

try:

Sub ZeroOneDashIandN()
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("01-IN", "02-IN", "03-IN") ', "04-IN", "05-IN", "06-IN",
"07-IN", "08-IN", "09-IN", "10-IN")
Application.ScreenUpdating = False

j = Sheets("01-IN").Range("B9").Value
For i = LBound(MyArr) To UBound(MyArr)
With Sheets("Sales Forecast")
lr = .Cells(.Rows.Count, 11).End(xlUp).Row
Set rngB = .Range("B13: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:=74) = varOut

End If
Next 'c

End With
Next 'i

Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Tue, 10 Dec 2013 20:12:07 +0100 schrieb Claus Busch:

Sheets(MyArr(i)).Range("B" & Rows.Count) _
.End(xlUp)(2).Resize(columnsize:=74) = varOut

sorry, typo. Change the line above:
Sheets(MyArr(i)).Range("B" & Rows.Count) _
.End(xlUp)(2).Resize(columnsize:=76) = varOut


Regards
Claus B.

 

[Howard]

Hi again,



Am Tue, 10 Dec 2013 20:12:07 +0100 schrieb Claus Busch:







sorry, typo. Change the line above:

Sheets(MyArr(i)).Range("B" & Rows.Count) _

.End(xlUp)(2).Resize(columnsize:=76) = varOut





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

OH yea, works nicely.

I see the omissions I made to set my code up to fail.

Question on the:

..End(xlUp)(2).Resize(columnsize:=76) = varOut

When you use columnsize:= does the : more or less Dim columnsize in place as a long?

And could you use it like this if you had some rows also?

..Resize(rowsize:=10,columnsize:=76) = varOut

Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 10 Dec 2013 12:36:36 -0800 (PST) schrieb Howard:

.End(xlUp)(2).Resize(columnsize:=76) = varOut

When you use columnsize:= does the : more or less Dim columnsize in place as a long?

columnsize (or rowsize) is a keyword. You don't need to dim it

And could you use it like this if you had some rows also?

.Resize(rowsize:=10,columnsize:=76) = varOut

If I have to resize both dimensions I don't use the keywords. Then I
write it as:
..Resize(10, 76) = varOut


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Tue, 10 Dec 2013 21:42:13 +0100 schrieb Claus Busch:

have a look into the VBA help. I don't know how these words are called
in the english language. In german language they are called "Named
Parameters". And for these parameters the values will be assigned with
":=". The same you e.g. have with Find and What:=, Lookin:= and so on


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Tue, 10 Dec 2013 21:42:13 +0100 schrieb Claus Busch:






have a look into the VBA help. I don't know how these words are called

in the english language. In german language they are called "Named

Parameters". And for these parameters the values will be assigned with

":=". The same you e.g. have with Find and What:=, Lookin:= and so on





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

I wondered if columnsize was a key word, but thought it would appear as ColumnResize instead of all lower case.

Thanks, I'll check it out.

Regards,
Howard