Simplifying VBA code
 

Hi All
The following code works fine, but for my information and education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M

Simplifying VBA code
 

maybe where col F =6

for i = 6 to 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Don Guillett
SalesAid Software

"Michael M" wrote in message
...
Hi All
The following code works fine, but for my information and education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M

Simplifying VBA code
 

Thanks Don
That's a little left field, but I'll try anything to improve by VBA knowledge
Regards
Michael M

"Don Guillett" wrote:

maybe where col F =6

for i = 6 to 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Don Guillett
SalesAid Software

"Michael M" wrote in message
...
Hi All
The following code works fine, but for my information and education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M

Simplifying VBA code
 

Don,

The original request was for columns F,H and I and therfore a step in the
for loop is required:

for i = 6 to 10 step 2
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Don Guillett" wrote:

maybe where col F =6

for i = 6 to 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Don Guillett
SalesAid Software

"Michael M" wrote in message
...
Hi All
The following code works fine, but for my information and education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M

Simplifying VBA code
 

Martin, Thanks for pointing that out.

--
Don Guillett
SalesAid Software

"Martin Fishlock" wrote in message
...
Don,

The original request was for columns F,H and I and therfore a step in the
for loop is required:

for i = 6 to 10 step 2
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Don Guillett" wrote:

maybe where col F =6

for i = 6 to 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Don Guillett
SalesAid Software

"Michael M" wrote in message
...
Hi All
The following code works fine, but for my information and education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M

Simplifying VBA code
 

But wouldn't this act on columns F, H and J ?

Pete

Martin Fishlock wrote:

Don,

The original request was for columns F,H and I and therfore a step in the
for loop is required:

for i = 6 to 10 step 2
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Don Guillett" wrote:

maybe where col F =6

for i = 6 to 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Don Guillett
SalesAid Software

"Michael M" wrote in message
...
Hi All
The following code works fine, but for my information and education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M

Simplifying VBA code
 

Pete

Thanks for pointing that out.

It makes the code a little more difficult to simplify, I would in this case
move it to a seperate subroutine and call them sepeately.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Pete_UK" wrote:

But wouldn't this act on columns F, H and J ?

Pete

Martin Fishlock wrote:

Don,

The original request was for columns F,H and I and therfore a step in the
for loop is required:

for i = 6 to 10 step 2
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Don Guillett" wrote:

maybe where col F =6

for i = 6 to 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Don Guillett
SalesAid Software

"Michael M" wrote in message
...
Hi All
The following code works fine, but for my information and education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M

Simplifying VBA code
 

Modifying Don's code slightly, you could do this:

for i = 6 to 9
If i = 7 Then i = 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i


Although I don't like modifying the loop control variable.

Pete

On Jan 24, 11:11 am, Martin Fishlock
wrote:
Pete

Thanks for pointing that out.

It makes the code a little more difficult to simplify, I would in this case
move it to a seperate subroutine and call them sepeately.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



"Pete_UK" wrote:
But wouldn't this act on columns F, H and J ?

Pete

Martin Fishlock wrote:

Don,

The original request was for columns F,H and I and therfore a step in the
for loop is required:

for i = 6 to 10 step 2
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

"Don Guillett" wrote:

maybe where col F =6

for i = 6 to 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Don Guillett
SalesAid Software

"Michael M" wrote in message
...
Hi All
The following code works fine, but for my information and education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M- Hide quoted text -- Show quoted text -

Simplifying VBA code
 

A novel (to me) approach. I kind of like it. Just tested with no bad result.
Got 6,8,9

Sub modifyloop()
For i = 6 To 9
If i = 7 Then i = 8
MsgBox i
'Cells(12, i).Copy
'Range(Cells(13, i), Cells(lrow - 1, i)).PasteSpecial (xlPasteFormats)
Next i
End Sub

--
Don Guillett
SalesAid Software

"Pete_UK" wrote in message
oups.com...
Modifying Don's code slightly, you could do this:

for i = 6 to 9
If i = 7 Then i = 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i


Although I don't like modifying the loop control variable.

Pete

On Jan 24, 11:11 am, Martin Fishlock
wrote:
Pete

Thanks for pointing that out.

It makes the code a little more difficult to simplify, I would in this
case
move it to a seperate subroutine and call them sepeately.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



"Pete_UK" wrote:
But wouldn't this act on columns F, H and J ?

Pete

Martin Fishlock wrote:

Don,

The original request was for columns F,H and I and therfore a step in
the
for loop is required:

for i = 6 to 10 step 2
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

"Don Guillett" wrote:

maybe where col F =6

for i = 6 to 8
cells(12,i).copy
range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats)
next i

--
Don Guillett
SalesAid Software

"Michael M" wrote in message
...
Hi All
The following code works fine, but for my information and
education, is
there a simpler way to write it ?

range("F12").Copy
range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats)
range("H12").Copy
range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats)
range("I12").Copy
range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats)

Thanks for any assistance
Michael M- Hide quoted text -- Show quoted text -