Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy range in macro using paste special values | Excel Discussion (Misc queries) | |||
copy/paste won't refer to the appropriate range of cells | Excel Discussion (Misc queries) | |||
How do I copy the contents of a range of text cells and paste into one cell? | Excel Discussion (Misc queries) | |||
Macro to copy, paste in a range and then delete | Excel Discussion (Misc queries) | |||
CheckBox Code to copy & paste range | Excel Discussion (Misc queries) |