copy range and paste into every 3rd cell of new range
 

I have 2 worksheets and I am looking for a macro that will copy a range of
cells from one worksheet, then paste them to another worksheet, but leave 2
blank cells between each cell.

sheet to copy from "MON" range: A7:A80
sheet to paste into "MON LOG" starting at cell A5, paste then leave 2 cells
blank, next cell A8, etc. until all of the copied range A7:A80 has been
pasted.

Any ideas?

copy range and paste into every 3rd cell of new range
 

This seems to work - not test extensively

Sub CopyJump()
myrange = Range("MON!A7:A80")
j = 5
For Each mycell In myrange
Worksheets("MON LOG").Cells(j, 1).Value = mycell
j = j + 3
Next
End Sub

bets wishes


--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"thomsonpa" wrote in message
...
I have 2 worksheets and I am looking for a macro that will copy a range of
cells from one worksheet, then paste them to another worksheet, but leave
2
blank cells between each cell.

sheet to copy from "MON" range: A7:A80
sheet to paste into "MON LOG" starting at cell A5, paste then leave 2
cells
blank, next cell A8, etc. until all of the copied range A7:A80 has been
pasted.

Any ideas?

copy range and paste into every 3rd cell of new range
 

Sorry I cannot get this to work, I need to insert it inot visual basic in the
middle of my code (inside a sub), I tried deleting the sub and end sub from
your code, but get error 1004. I have selected the range on "MON" sheet and
named it myrange, is this correct? Sorry i am a beginner and need more exact
instructions.

Many thanks for your prompt response.

Peter.

"Bernard Liengme" wrote:

This seems to work - not test extensively

Sub CopyJump()
myrange = Range("MON!A7:A80")
j = 5
For Each mycell In myrange
Worksheets("MON LOG").Cells(j, 1).Value = mycell
j = j + 3
Next
End Sub

bets wishes


--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"thomsonpa" wrote in message
...
I have 2 worksheets and I am looking for a macro that will copy a range of
cells from one worksheet, then paste them to another worksheet, but leave
2
blank cells between each cell.

sheet to copy from "MON" range: A7:A80
sheet to paste into "MON LOG" starting at cell A5, paste then leave 2
cells
blank, next cell A8, etc. until all of the copied range A7:A80 has been
pasted.

Any ideas?

copy range and paste into every 3rd cell of new range
 

Well I have got this to work a treat now, many thanks. I used the call
method to run the macro when I wanted it to run.

Mnay thanks.

"thomsonpa" wrote:

Sorry I cannot get this to work, I need to insert it inot visual basic in the
middle of my code (inside a sub), I tried deleting the sub and end sub from
your code, but get error 1004. I have selected the range on "MON" sheet and
named it myrange, is this correct? Sorry i am a beginner and need more exact
instructions.

Many thanks for your prompt response.

Peter.

"Bernard Liengme" wrote:

This seems to work - not test extensively

Sub CopyJump()
myrange = Range("MON!A7:A80")
j = 5
For Each mycell In myrange
Worksheets("MON LOG").Cells(j, 1).Value = mycell
j = j + 3
Next
End Sub

bets wishes


--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"thomsonpa" wrote in message
...
I have 2 worksheets and I am looking for a macro that will copy a range of
cells from one worksheet, then paste them to another worksheet, but leave
2
blank cells between each cell.

sheet to copy from "MON" range: A7:A80
sheet to paste into "MON LOG" starting at cell A5, paste then leave 2
cells
blank, next cell A8, etc. until all of the copied range A7:A80 has been
pasted.

Any ideas?

copy range and paste into every 3rd cell of new range
 

Thanks for the feedback.
For future reference: when you see RANGE("myrange") the code is referring to
a named range on the worksheet but Myrange on its own is a VBA variable like
j in my code.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"thomsonpa" wrote in message
...

Well I have got this to work a treat now, many thanks. I used the call
method to run the macro when I wanted it to run.

Mnay thanks.

"thomsonpa" wrote:

Sorry I cannot get this to work, I need to insert it inot visual basic in
the
middle of my code (inside a sub), I tried deleting the sub and end sub
from
your code, but get error 1004. I have selected the range on "MON" sheet
and
named it myrange, is this correct? Sorry i am a beginner and need more
exact
instructions.

Many thanks for your prompt response.

Peter.

"Bernard Liengme" wrote:

This seems to work - not test extensively

Sub CopyJump()
myrange = Range("MON!A7:A80")
j = 5
For Each mycell In myrange
Worksheets("MON LOG").Cells(j, 1).Value = mycell
j = j + 3
Next
End Sub

bets wishes


--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"thomsonpa" wrote in message
...
I have 2 worksheets and I am looking for a macro that will copy a
range of
cells from one worksheet, then paste them to another worksheet, but
leave
2
blank cells between each cell.

sheet to copy from "MON" range: A7:A80
sheet to paste into "MON LOG" starting at cell A5, paste then leave 2
cells
blank, next cell A8, etc. until all of the copied range A7:A80 has
been
pasted.

Any ideas?