how to copy a row of cells automatically from one worksheet to another by changing the value of1cell

[david willis]

Hello.
I have 8 cells on the same row in one worksheet that I would like to be
automatically copied to a predefined area in one of 3 other worksheets
in my file depending on the number (1, 2 or 3) entered in one cell
adjacent to these 8 cells.
Is this possible and, if so, how could it be done? Thanks very much for
any help you can offer me with this.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Bernie Deitrick]

David,

You could use the worksheet change event: this will copy the eight cells
from the same row, adjacent to column A, with the worksheet based on the
value entered in column A. Assumes you have a Sheet1, Sheet2, and Sheet3,
and you want to copy the values to A1:H1 on those sheets. Copy the code,
right-click on the sheet tab of the source sheet, select 'View code' and
paste the code into the window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1).Resize(1, 8).Copy _
Worksheets("Sheet" & Target.Value).Range("A1")
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP