Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro/vba
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro/vba
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 "Anna" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro/vba
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 "Anna" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro/vba
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 "Mark Ivey" wrote in message ... 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 "Anna" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro/vba
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 -- JB "Joel" wrote: 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 "Anna" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |