Code issue

F

fgwiii

I have modified some existing code and for some reason when the paste happens
instead of only pasting to the "D" column, it is pasting to multiple columns
and throwing my data out of format. Can you please tell me what is wrong and
how do I fix it?

ActiveWindow.LargeScroll Down:=1
Range("J23790").Select
ActiveCell.FormulaR1C1 = "1"
Range("J23790").Select
Selection.NumberFormat = "0"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

Range("J23790").Select
Selection.Copy
Range("D2:D23501").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Font
.Name = "Tahoma"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With


Thank you,

Fred
 
M

Mike H

Hi,

I tidied you recorded code up a bit and it should now do what you want

Sub Sonic()
With Range("J23790")
.Value = 1
.NumberFormat = "0"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Copy
End With

With Range("D2:D23501")
.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
End With

With Range("D2:D23501").Font
.Name = "Tahoma"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub


Mike
 
F

fgwiii

Hello,

I placed the code in and it still appears to do the same thing. the format
is pasted Columns C through G with the format of "0".

I stepped through it and when it gets to :
With Range("D2:D23501")
..PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply,

the format gets pasted.

Just in case something else is hosing this, here is the rest of the code
prior to the code you corrected:

ActiveWindow.LargeScroll Down:=40
Range("J23790").Select
ActiveCell.FormulaR1C1 = "1"
Range("J23790").Select
Selection.NumberFormat = "0"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Range("J23790")
..Value = 1
..NumberFormat = "0"
..Borders(xlDiagonalDown).LineStyle = xlNone
..Borders(xlDiagonalUp).LineStyle = xlNone
..Copy
End With

With Range("D2:D23501")
..PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
End With

With Range("D2:D23501").Font
..Name = "Tahoma"
..Size = 12
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
End With

Thanks
 

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

Top