macro/vba

[Anna]

I have a list (column) of text that i need to copy into the same cell
reference on multiple sheets;
Each cell in the column needs to go to a different sheet (same cell
reference, different sheet)i.e: first entry of the column should go into cell
B1 in sheet 1, second entry should go into cell B1 but sheet 2, 3d entry in
the column should go into cell B1 in sheet 3 and so on,there are 95 entries
in this column that need to go into 95 sheets

Any ideas?

 

[Mark Ivey]

Here is something you can try. Change the myRange value to fit what you need
copied.

Mark Ivey


Sub copy_column_to_all_sheets()
Dim mySheet As String
Dim sh As Worksheet
Dim myRange As String

mySheet = Sheets(1).Name
myRange = "B1:B3"

For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> mySheet Then
With sh
.Range(myRange).Value = Sheets(mySheet).Range(myRange).Value
End With
End If
Next sh

End Sub

 

[Joel]

There are a few ways to do this

for i = 1 to 95
sheets("Sheet" & i).Range("B1") = Activesheet.Range("B" & i)
next i

I don't know how you have the sheets named. The above code assumes the
sheets are named Sheet1 - sheet95

If the sheets are in order and you want to use the index number of the sheet
use the code below. The index number is the order of the sheets in the
workbook. Normally Sheet1 is index 1 and sheet3 is index 3. But you can
move sheets around in the workbookin any order so these number aren't always
the same

for i = 1 to 95
sheets(i).Range("B1") = Activesheet.Range("B" & i)
next i

 

[Mark Ivey]

Oops... misunderstood your request.

Here is the correct solution.


Mark Ivey


Sub copy_column_to_all_sheets()
Dim mySheet As String
Dim sh As Worksheet
Dim i As Long

mySheet = Sheets(1).Name

i = 1

For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> mySheet Then
With sh
sh.Cells(1, 2).Value = Sheets(mySheet).Cells(i, 2).Value
End With
i = i + 1
End If
Next sh

End Sub

 

[john]

another way:

Sub CopyColData()
Dim wsDS As Worksheet
On Error Resume Next
'change sheet name as required
Set wsDS = Worksheets("Data Sheet")
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> wsDS.Name Then
With wsDS
'assume your data is in col A
'if not change as required
.Range("A1:A" & .Range("A" & .Rows.Count) _
.End(xlUp).Row).Copy Sheets(sh.Name).Range("B1")
End With
End If
Next
On Error GoTo 0
End Sub