Jim,

That code goes into each worksheet's own code section that you need it to be
used on. That's why I said copy it from the posting above, right click on
the "Budget" sheet tab and choose [View Code] from the list that appears and
paste it into the (probably) empty sheet that appears in the editor. It is
called, not as a 'macro' per se, but as a response by Excel to a change in a
cell in column A of that sheet. For multiple sheets, a copy of that code
would have to be placed into each equivalent of the 'Budget' sheet. It also
assumes that both the 'Budget' sheet(s) and the Data sheet are all in the
same workbook.

You might take a look at the solution Max offers - it might be more useful
to you if you are dealing with multiple sheets and multiple workbooks.
However, if you are working with multiple workbooks, then the book the 'Data'
sheet is in will be linked to each of the other workbooks referenced in the
formula. The big problem with his offering, is that if the 'Budget' sheet(s)
are in other workbooks, the formula cannot work properly unless those
workbooks are open at the same time the one with the 'Data' sheet is open.
Offset() doesn't work across workbooks unless both are open.

If you are working with multiple workbooks and want to do the data copying
'on demand' by the user by choosing Tools | Macro | Macros and picking the
macro from the list type of operation, let us know. That can be arranged.



"Jim G" wrote:

Thanks for the response.
When I try to run the code it asks for a macro. Do I need to do anything
else?

I have several budget sheets to add to the data sheet I then import the lot
into our accounting software (12 mths x 100 accounts x 4 divisions). Will
this code run for each sheet separately and add to the data sheet? If the
import gets too cumbersome I will use separate import files for each division.

Cheers


--
Jim


"JLatham" wrote:

Taking your request literally. ANY change in column A will result in the
action - even new additions at the bottom of the existing list. To put this
code into use, right click on the Budget sheet's tab and choose View Code,
cut and paste this into it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestRange As Range
Dim Offset1 As Long
Dim Offset2 As Long
Dim LC As Integer

If Target.Column < 1 Then
Exit Sub
End If
Application.EnableEvents = False
Set DestRange = Worksheets("Data").Range("A1")
If IsEmpty(DestRange) Then
Offset2 = 0
Else
Offset2 = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
End If
Offset1 = 0
Do While Not (IsEmpty(Target.Offset(Offset1, 0)))
For LC = 1 To 12
DestRange.Offset(Offset2, 0) = Target.Offset(Offset1, 0)
Offset2 = Offset2 + 1
Next
Offset1 = Offset1 + 1
Loop
Application.EnableEvents = True
End Sub


"Jim G" wrote:

I have a list of unique accounts in column A on a worksheet "Budget".

I wish to copy each account number from the worksheet "Budget" to another
worksheet called "Data" over 12 rows in column A then when the value changes
copy the next value down the next 12 rows and so on until the next value is
blank or empty.

The result would be the list of accounts in Col A of "Budget" are repeated
12 times each in Column A of worksheet "Data".

Any help would be much appreciated.

--
Jim