Thread: 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 don’t 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

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 don’t 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
>
>

Sorry, I missed something (need to select the sheet to copy to). See the
modified line immediately after "If i>4 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 don’t 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
> >
> >

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 don’t 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
> >
> >

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 i>4 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 don’t 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
> > >
> > >

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 don’t 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
> > >
> > >