| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic copy range
Afternoon,
I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
Sheets("1").Range("P10:AA109").Copy for index = 2 top 10 worksheets(index).Range("P10") Sheets("2").Range("P10").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next "LiAD" wrote: Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
Try the below which will copy the format from the 1st sheet to n sheets
Sub Macro() Dim ws As Worksheet, intSheet As Integer Sheets(1).Range("P10:AA109").Copy For intSheet = 2 To Worksheets.Count Sheets(intSheet).Range("P10").PasteSpecial Paste:=xlPasteFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Next Application.CutCopyMode = False End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
excuse typo -- new keyboard ;)
for index = 2 TO 10 worksheets(index).Range("P10").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next "Patrick Molloy" wrote: Sheets("1").Range("P10:AA109").Copy for index = 2 top 10 worksheets(index).Range("P10") Sheets("2").Range("P10").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next "LiAD" wrote: Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
Here is another way to do what you want...
Sub CopyFormats() Dim X As Long For X = 2 To Worksheets.Count Worksheets(1).Range("P10:AA109").Copy Worksheets(X).Range("P10") Worksheets(X).Range("P10:AA109").ClearContents Next End Sub This code assumes your "Sheet 1" is the first sheet tab on the left. If that is not the case, let me know and I'll adjust the code to handle it (but let me know the exact sheet name though so I can build my code around it). Also note that I assumed you meant AA109 when you wrote A109). -- Rick (MVP - Excel) "LiAD" wrote in message ... Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
Sorry,
I had forgotten that I have three worksheets that I do not want to copy the format to. I have sheets a,b,c then sheets 1,2,3. I want to copy from 1 to 2,3 etc.... Do I have to name each sheet I want or how its best to go about this? Thanks "Jacob Skaria" wrote: Try the below which will copy the format from the 1st sheet to n sheets Sub Macro() Dim ws As Worksheet, intSheet As Integer Sheets(1).Range("P10:AA109").Copy For intSheet = 2 To Worksheets.Count Sheets(intSheet).Range("P10").PasteSpecial Paste:=xlPasteFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Next Application.CutCopyMode = False End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
Sorry,
I had forgotten that I have three worksheets that I do not want to copy the format to. I have sheets a,b,c then sheets 1,2,3. I want to copy from 1 to 2,3 etc.... Do I have to name each sheet I want or how its best to go about this? Thanks "Rick Rothstein" wrote: Here is another way to do what you want... Sub CopyFormats() Dim X As Long For X = 2 To Worksheets.Count Worksheets(1).Range("P10:AA109").Copy Worksheets(X).Range("P10") Worksheets(X).Range("P10:AA109").ClearContents Next End Sub This code assumes your "Sheet 1" is the first sheet tab on the left. If that is not the case, let me know and I'll adjust the code to handle it (but let me know the exact sheet name though so I can build my code around it). Also note that I assumed you meant AA109 when you wrote A109). -- Rick (MVP - Excel) "LiAD" wrote in message ... Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
two replies suggest that you do NOT want to copy the format?
your question indicated that you ONLY wanted to copy the format. If you want just formula then change XLPasteFormats to xlPasteFormulas note, more btraodly: you can combine also: with Sheets("2").Range("P10") ..PasteSpecial xlPasteValues ..PasteSpecial xlPasteFormats end with "LiAD" wrote: Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
If the sheets are named as a,b,c,1,2,3,4,5,6,7,8,9,10 and you want to copy
from 1 to 2-10 then try..Adjust to suit... Sub Macro() Dim ws As Worksheet, intSheet As Integer Sheets("1").Range("P10:AA109").Copy For intSheet = 2 To 10 Sheets(Cstr(intSheet)).Range("P10").PasteSpecial Paste:=xlPasteFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Next Application.CutCopyMode = False End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below which will copy the format from the 1st sheet to n sheets Sub Macro() Dim ws As Worksheet, intSheet As Integer Sheets(1).Range("P10:AA109").Copy For intSheet = 2 To Worksheets.Count Sheets(intSheet).Range("P10").PasteSpecial Paste:=xlPasteFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Next Application.CutCopyMode = False End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
Yes I want to copy only the format ONLY (specifically only the borders
actually), no formulas or anything else. The formulas I tried, (was given by you three guys), copied it to all 10 sheets, I only want to copy it to specific sheets with the file. Do I need therefore to provide a list of the sheet names i wish to use or how do i limit to which sheets it copies? Otherwise it is copying from the fourth sheet to the 11th. Sheets 12-15 shoud not be formatted. Thanks "Patrick Molloy" wrote: two replies suggest that you do NOT want to copy the format? your question indicated that you ONLY wanted to copy the format. If you want just formula then change XLPasteFormats to xlPasteFormulas note, more btraodly: you can combine also: with Sheets("2").Range("P10") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats end with "LiAD" wrote: Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
Whether you have to list the names or not depends on the *exact* naming
system you used. Are your sheets really *named* a, b, c, 1, 2, 3, etc.? If not, tell us the *actual* names you are using. Alternately, **IF** your sheets are in the order you have shown, namely the left three tabs are for your a, b, c sheets, and the fourth tab is your 1 sheet, then it doesn't matter what their names are as we can work with their position numbers (given they are in the order I just assumed). That code would be... Sub CopyFormats() Dim X As Long For X = 5 To Worksheets.Count Worksheets(4).Range("P10:AA109").Copy Worksheets(X).Range("P10") Worksheets(X).Range("P10:AA109").ClearContents Next End Sub -- Rick (MVP - Excel) "LiAD" wrote in message ... Sorry, I had forgotten that I have three worksheets that I do not want to copy the format to. I have sheets a,b,c then sheets 1,2,3. I want to copy from 1 to 2,3 etc.... Do I have to name each sheet I want or how its best to go about this? Thanks "Rick Rothstein" wrote: Here is another way to do what you want... Sub CopyFormats() Dim X As Long For X = 2 To Worksheets.Count Worksheets(1).Range("P10:AA109").Copy Worksheets(X).Range("P10") Worksheets(X).Range("P10:AA109").ClearContents Next End Sub This code assumes your "Sheet 1" is the first sheet tab on the left. If that is not the case, let me know and I'll adjust the code to handle it (but let me know the exact sheet name though so I can build my code around it). Also note that I assumed you meant AA109 when you wrote A109). -- Rick (MVP - Excel) "LiAD" wrote in message ... Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
Job done.
Thanks for your help! "Rick Rothstein" wrote: Whether you have to list the names or not depends on the *exact* naming system you used. Are your sheets really *named* a, b, c, 1, 2, 3, etc.? If not, tell us the *actual* names you are using. Alternately, **IF** your sheets are in the order you have shown, namely the left three tabs are for your a, b, c sheets, and the fourth tab is your 1 sheet, then it doesn't matter what their names are as we can work with their position numbers (given they are in the order I just assumed). That code would be... Sub CopyFormats() Dim X As Long For X = 5 To Worksheets.Count Worksheets(4).Range("P10:AA109").Copy Worksheets(X).Range("P10") Worksheets(X).Range("P10:AA109").ClearContents Next End Sub -- Rick (MVP - Excel) "LiAD" wrote in message ... Sorry, I had forgotten that I have three worksheets that I do not want to copy the format to. I have sheets a,b,c then sheets 1,2,3. I want to copy from 1 to 2,3 etc.... Do I have to name each sheet I want or how its best to go about this? Thanks "Rick Rothstein" wrote: Here is another way to do what you want... Sub CopyFormats() Dim X As Long For X = 2 To Worksheets.Count Worksheets(1).Range("P10:AA109").Copy Worksheets(X).Range("P10") Worksheets(X).Range("P10:AA109").ClearContents Next End Sub This code assumes your "Sheet 1" is the first sheet tab on the left. If that is not the case, let me know and I'll adjust the code to handle it (but let me know the exact sheet name though so I can build my code around it). Also note that I assumed you meant AA109 when you wrote A109). -- Rick (MVP - Excel) "LiAD" wrote in message ... Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dynamic copy range
probably best to have a table with the sheet numbers in it
say its called table1 on ther control sheet dim cell as range Sheets("1").Range("P10:AA109").Copy For Each cell in worksheets("control").Range("Table1").Cells Worksheets(cell.Value).Range("P10").PasteSpecial xlPasteFormats Next "LiAD" wrote: Yes I want to copy only the format ONLY (specifically only the borders actually), no formulas or anything else. The formulas I tried, (was given by you three guys), copied it to all 10 sheets, I only want to copy it to specific sheets with the file. Do I need therefore to provide a list of the sheet names i wish to use or how do i limit to which sheets it copies? Otherwise it is copying from the fourth sheet to the 11th. Sheets 12-15 shoud not be formatted. Thanks "Patrick Molloy" wrote: two replies suggest that you do NOT want to copy the format? your question indicated that you ONLY wanted to copy the format. If you want just formula then change XLPasteFormats to xlPasteFormulas note, more btraodly: you can combine also: with Sheets("2").Range("P10") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats end with "LiAD" wrote: Afternoon, I have a file with 10 different sheets in it. I have a macro part of which is attached below, which copies the format from a set of cells in sheet 1 and copies them into sheet 2. I need to do this for all 10 sheets - take the format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2, then sheet 3, sheet 4 etc etc. All copying is from sheet 1. Is it possible to have one formula that will loop through the sheets without me having to repeat the same code 10 times? Thanks Sheets("1").Range("P10:AA109").Copy Sheets("2").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next code would be (exactly the same apart from sheet ref in second line Sheets("1").Range("P10:AA109").Copy Sheets("3").Range("P10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy dynamic range | Excel Programming | |||
| Macro to copy a certain a cell to a dynamic range | Excel Programming | |||
| copy a dynamic column range in macro? | Excel Programming | |||
| Copy via code from a dynamic range using offeset | Excel Programming | |||
| Dynamic range copy. | Excel Programming | |||
Linear Mode
