Macro to copy cells from one column to multiple sheets

[jonathansd]

Hi guys,
I need to create a macro to copy cells from a column to multiple sheets. For example, i need to copy
A1 from sheet0 to sheet1,
A2 from sheet0 to sheet2,
A3 from sheet0 to sheet3 etc
What i need to do is sort of a reverse summary
I need to do this for 1000 sheets. I am using Excel 2013
Hope you guys can help.

 

[venkat1926]

do an experiment
open a new work book
in sheet1 in column A from A1 downward type following

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
open more sheets sheet4 sheet5 and sheet6

now copy this macro in vb editor of this file, save is as macro enabled file and run the macro and see what happens in sheet 2 to sheet 6
if this is what you want you can edit the macro for your use

Sub test()
Dim r As Range, c As Range, j As Long, x, rrow As Long
With Worksheets("sheet1")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
For Each c In r
rrow = c.Row
x = c
 j = rrow + 1
With Worksheets(j)


.Cells(rrow, 1) = x
End With
Next c
End With
End Sub

 

[jonathansd]

Thanks venkat. This script is writing as follows;

A1 from sheet1 to A1 on sheet2
A2 from sheet1 to A2 on sheet3
A3 from sheet1 to A3 on sheet4

I need it to copy all the data to A1 on the other sheets.
Which part of the script is to go to the next row on the other sheets?

 

[venkat1926]

TRY THIS MODIFIED AND CALLED "MACAROONE"

is this what you want

Sub testONE()
Dim r As Range, c As Range, j As Long, x, rrow As Long
With Worksheets("sheet1")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
For Each c In r
rrow = c.Row
x = c
 j = rrow + 1
With Worksheets(j)




.Cells(1, 1) = x
End With
Next c
End With
End Sub

 

[jonathansd]

yes! Thank you! You're awesome!

 

[jonathansd]

Hi venkat,
The macro works well with the test but i can't seem to get it to work for my project.
The macro runs, but no changes are made to the sheets.
I need the data from the worksheet "Master List" cells D4 to D1004 to be copied in the B2 cells of sheets "0, 1, 2, 3 till 999"
I can's seem to figure it out. Hope you can help. Thank you for your time.

 

[venkat1926]

did you modify the macro to suit you

my data sheet is SHEET1. yours is something else

my data (r) is A1 down yours is somewere in D

modify macro to suit you. If it still does not work send a very small extract of your master list and also your modified macro.

 

[jonathansd]

Hi again venkat,

The modified macro thati used was as below

Sub testONE()
Dim r As Range, c As Range, j As Long, x, rrow As Long
With Worksheets("Master List")
Set r = Range(.Range("D4"), .Range("B2").End(xlDown))
For Each c In r
rrow = c.Row
x = c
j = rrow + 1
With Worksheets(j)

In the screenshot below, i am trying to copy the data from the column in blue to the column in yellow.
Thanks again!

 

[venkat1926]

see this code


Set r = Range(.Range("D4"), .Range("B2").End(xlDown))


do you see the mistake second range is B2 wrong it should be D4


further into my code you see a code


.Cells(1, 1) = x


what is cells(1,1) is A1 in the other sheet you dont wnat that you want .cells(2,2) (that is B2).tht dot is required becase code uses
with worksheets(j)


make these corrections and try.


I cannot tranfer image to excel sheet to check.
if you cannot use add in to send data, send the fille containg first three sheets in a file and upload to speedysahre.com and post the uploaded address
(normally this will not be recommended)
</pre>

 

[jonathansd]

Works great! The mistakes that i made were setting the range as well as the cell to write the data on. Thank you!