Transfer info from one sheet to another depending on cell input

[manmah]

Hi there

Think I should change my user name to 'stumped again!!'

I am wanting to transfer data from one sheet to another depending on cell input

example:

If I enter "2" in sheet1 cell B1, then I want a macro to take A1 to sheet2 but place it in A1 & A2.
then say I enter "1" in sheet1 cell B2 goto sheet2 and put A2 into A3 and so on.

Depending on the number entered on sheet1 column B, the Macro duplicates the info from Column A in that row into column A on sheet2. ?????

Thanks in advance

Mark

 

[Mohammad Basem]

Hi Mark,

Try this, copy the code to Sheet1 code window

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lEmptyRow As Long, LCounter As Long

    If Target.Column = 2 And Target.Count = 1 Then
        If Target <= 0 Then Exit Sub
        With Sheets("Sheet2")
            If .Range("A1") = "" Then
                lEmptyRow = 1
            Else
                lEmptyRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1).Row
            End If
            For LCounter = 1 To Target
                .Cells(lEmptyRow + LCounter - 1, "A") = Target.Offset(, -1)
            Next LCounter
        End With
    End If

End Sub[/COLOR][/SIZE][/FONT]

 

[manmah]

Brilliant..thankyou very much!!!!!

 

[Mohammad Basem]

Al-hamdullah, thank you for the feedback!

 

[manmah]

You'e welcome.. one more quick question tho, i've been fiddling around and just wondered what I would change in the code if I have another column in between. eg - my number in M and the info to copy in K??


Thanks
Mark

 

[manmah]

Don't worry done it !!!

Thanks