Action on every sheet

[bturner2]

Need some insight for a newbie.

What I would like to do is create a generic vba script that would allow me to copy information from sheet A to a designated area in all of the other spread sheets within a workbook regardless of the sheets position or name.

So let?s say I have a workbook with 40 sheets in it that are named differently. I would like to copy information from sheet "A" range "a1..aw31" and paste it to every sheet at range z1.

Once I have this basic script I think I can modify it to accommodate any other needs that I may have.

Any help from the guru?s?

[Bob Phillips]

Off the top


Sub CopyData()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> ActiveSheet.Name Then
ActiveSheet.Range("A1:AW31").Copy _
sh.Range("Z1")
End If
Next sh
End Sub

[bturner2]

hmmm......nope doesnt seem to work. it is not giving me any errors. It is just not stepping to each sheet and pasting the information. Any further ideas.

By the way thanks for helping out. You guys do a great job on here.

[Bob Phillips]

hmmm......nope doesnt seem to work. it is not giving me any errors. It is just not stepping to each sheet and pasting the information. Any further ideas.

By the way thanks for helping out. You guys do a great job on here.

Is Sheet A active when you run it?

[Zack Barresse]

Maybe if you explicitly had your sheet name in there ...


Option Explicit

Sub CopyDataDos()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "A" Then
Sheets("A").Range("A1:AW31").Copy sh.Range("Z1")
End If
Next sh
Application.CutCopyMode = False
End Sub

[bturner2]

Ok I finaly made it back. I have tried both examples that were given with the A sheet being active and neither examples seem to work. The code that XLD provided does copy the information to the very first blank page but seems to quit after it has pasted the information in the sheet. I dont know if it matters but I am trying to do this in excel 202 (10.6501.6626)sp3.

[BDavidson]

The code that XLD provided does copy the information to the very first blank page but seems to quit after it has pasted the information in the sheet.

How many worksheets (tabs) do you have in that workbook?

[lucas]

Zacks code works for me in Excel 2000. Your first sheet must be named A of course or you have to change that in the code....:hi:

[Zack Barresse]

Well Hiya Barrie!!!!! Great to see ya here!! :yes

[BDavidson]

Well Hiya Barrie!!!!! Great to see ya here!! :yes

Thanks Zack, looking forward to helping out where I can!
:beerchug:

[bturner2]

You are absolutly correct that the code does work. I guess I was not thinking when it came to the range Z1. I threw that in there as an example and did not catch it untill after you had posted that the code does work for you and looked at it for the umteenth time to see what I was doing wrong. Thanks for the help guys