Using VBA to copy unique values across multiple sheets

[Excels Ghost]

Hi Experts,

I have a situation where I am looking to copy data from one sheet across a range of cells on multiple other sheets but the copied value is dynamic.

So --
Sheet1 has a list of items that I want copied to other sheets, starting in cell A1.
Sheet2 has a range of B1:Y1 that I need the value A1 from Sheet1 copied to.
Sheet3 has the same range of B1:Y1 that needs copied to but I need it to use A2 from Sheet1.
Sheet4 is the same range but use A3 Sheet1, etc.

I need this process to repeat for all active sheets except Sheet1 should be excluded.

Any help would be very much appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub ExcelsGhost()
   Dim i As Long
   For i = 2 To Sheets.Count
      Sheets("Sheet" & i).Range("B1:Y1").Value = Sheets("Sheet1").Range("A" & i - 1)
   Next i
End Sub

 

[Excels Ghost]

Hi and thank you.

I tried that out and got a subscript out of range error on the line below. I'm still pretty new to VBA so sorry for my ignorance on this error, but any thoughts as to what maybe causing it?

[/FONT]Sheets("Sheet" & i).Range("B1:Y1").Value = Sheets("Sheet1").Range("A" & i - 1)

 

[Fluff]

It means that one of the sheets doesn't exist in the workbook.
Are your sheets actually called (as seen on the sheet tab) Sheet1, Sheet2 etc?

 

[Excels Ghost]

They are actually Sheet1, Sheet1 (2), Sheet1 (3), a macro copied them

 

[Fluff]

Ok, how about

Sub ExcelsGhost()
   Dim i As Long
   For i = 2 To Sheets.Count
      Sheets("Sheet1 (" & i & ")").Range("B1:Y1").Value = Sheets("Sheet1").Range("A" & i - 1)
   Next i
End Sub

 

[Excels Ghost]

That worked - thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback