| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Items to All but Several Workshets
Hello,
I'm trying to create a macro that would paste items from a sheet, say Sheet1 range A1:D1 to some of the other worksheets, say Sheets5-Sheet10 Range A1:D1. I came up with the following macro: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy For i = 1 To Worksheets.Count Select Case PasteToWorksheets Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" Case Else Range("E10").Select ActiveSheet.Paste End Select Next i End Sub That macro generates an error and the line Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" is highlighted. I did not enter any code for the case when the worksheet is Sheet1, Sheet2, Sheet3, or Sheet4 because I dont want to paste items into those worksheets. I'm not sure how to fix this. Any advice you could give would be greatly appreciated! Sincerely, Magnivy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Paste Items to All but Several Workshets
How about this:
Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Range("E10").Select ActiveSheet.Paste End Next i End Sub "Magnivy" wrote: Hello, I'm trying to create a macro that would paste items from a sheet, say Sheet1 range A1:D1 to some of the other worksheets, say Sheets5-Sheet10 Range A1:D1. I came up with the following macro: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy For i = 1 To Worksheets.Count Select Case PasteToWorksheets Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" Case Else Range("E10").Select ActiveSheet.Paste End Select Next i End Sub That macro generates an error and the line Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" is highlighted. I did not enter any code for the case when the worksheet is Sheet1, Sheet2, Sheet3, or Sheet4 because I dont want to paste items into those worksheets. I'm not sure how to fix this. Any advice you could give would be greatly appreciated! Sincerely, Magnivy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Paste Items to All but Several Workshets
Sorry, I missed something (need to select the sheet to copy to). See the
modified line immediately after "If i4 then". Sorry about that. Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Sheets(i).Range("E10").Select ActiveSheet.Paste End Next i End Sub "Paul Mathews" wrote: How about this: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Range("E10").Select ActiveSheet.Paste End Next i End Sub "Magnivy" wrote: Hello, I'm trying to create a macro that would paste items from a sheet, say Sheet1 range A1:D1 to some of the other worksheets, say Sheets5-Sheet10 Range A1:D1. I came up with the following macro: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy For i = 1 To Worksheets.Count Select Case PasteToWorksheets Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" Case Else Range("E10").Select ActiveSheet.Paste End Select Next i End Sub That macro generates an error and the line Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" is highlighted. I did not enter any code for the case when the worksheet is Sheet1, Sheet2, Sheet3, or Sheet4 because I dont want to paste items into those worksheets. I'm not sure how to fix this. Any advice you could give would be greatly appreciated! Sincerely, Magnivy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Paste Items to All but Several Workshets
Okay, sorry again, one more kick at the can (it's a brain freeze day for me):
Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Sheets(i).Select Range("E10").Select ActiveSheet.Paste End Next i End Sub "Paul Mathews" wrote: How about this: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Range("E10").Select ActiveSheet.Paste End Next i End Sub "Magnivy" wrote: Hello, I'm trying to create a macro that would paste items from a sheet, say Sheet1 range A1:D1 to some of the other worksheets, say Sheets5-Sheet10 Range A1:D1. I came up with the following macro: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy For i = 1 To Worksheets.Count Select Case PasteToWorksheets Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" Case Else Range("E10").Select ActiveSheet.Paste End Select Next i End Sub That macro generates an error and the line Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" is highlighted. I did not enter any code for the case when the worksheet is Sheet1, Sheet2, Sheet3, or Sheet4 because I dont want to paste items into those worksheets. I'm not sure how to fix this. Any advice you could give would be greatly appreciated! Sincerely, Magnivy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Paste Items to All but Several Workshets
Hey Paul,
Your macro works. Thaks a lot for your help! Magnivy "Paul Mathews" wrote: Sorry, I missed something (need to select the sheet to copy to). See the modified line immediately after "If i4 then". Sorry about that. Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Sheets(i).Range("E10").Select ActiveSheet.Paste End Next i End Sub "Paul Mathews" wrote: How about this: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Range("E10").Select ActiveSheet.Paste End Next i End Sub "Magnivy" wrote: Hello, I'm trying to create a macro that would paste items from a sheet, say Sheet1 range A1:D1 to some of the other worksheets, say Sheets5-Sheet10 Range A1:D1. I came up with the following macro: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy For i = 1 To Worksheets.Count Select Case PasteToWorksheets Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" Case Else Range("E10").Select ActiveSheet.Paste End Select Next i End Sub That macro generates an error and the line Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" is highlighted. I did not enter any code for the case when the worksheet is Sheet1, Sheet2, Sheet3, or Sheet4 because I dont want to paste items into those worksheets. I'm not sure how to fix this. Any advice you could give would be greatly appreciated! Sincerely, Magnivy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Paste Items to All but Several Workshets
No problem. I got it to work. Thanks again!
Magnivy "Paul Mathews" wrote: Okay, sorry again, one more kick at the can (it's a brain freeze day for me): Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Sheets(i).Select Range("E10").Select ActiveSheet.Paste End Next i End Sub "Paul Mathews" wrote: How about this: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy 'Copy data to all sheets except sheets 1-4 For i = 1 To Worksheets.Count If i 4 then Range("E10").Select ActiveSheet.Paste End Next i End Sub "Magnivy" wrote: Hello, I'm trying to create a macro that would paste items from a sheet, say Sheet1 range A1:D1 to some of the other worksheets, say Sheets5-Sheet10 Range A1:D1. I came up with the following macro: Sub PASTEFORMULAS() Worksheets("Sheet1").Select Range("A1:D1").Select Selection.Copy For i = 1 To Worksheets.Count Select Case PasteToWorksheets Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" Case Else Range("E10").Select ActiveSheet.Paste End Select Next i End Sub That macro generates an error and the line Case Worksheets(i).Name = "Sheet1" Or "Sheet2" Or "Sheet3" Or "Sheet4" is highlighted. I did not enter any code for the case when the worksheet is Sheet1, Sheet2, Sheet3, or Sheet4 because I dont want to paste items into those worksheets. I'm not sure how to fix this. Any advice you could give would be greatly appreciated! Sincerely, Magnivy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to paste multipule items | Excel Discussion (Misc queries) | |||
| can't sum up items i cut&paste from access to excel.. | Excel Discussion (Misc queries) | |||
| cut and paste filtered items | Excel Discussion (Misc queries) | |||
| Paste Multiple items | New Users to Excel | |||
| Using VBA to Paste items copied within Excel | Excel Programming | |||
Linear Mode
