To use Coloumns to Paste to Sheets

[btsp]

So i have 3 Coloumns (F-H), they are checkmark coloumns.

Now i want it so that if i click Coloumn F in any row it will paste to "sheet 2".
And "sheet 3" for G and "sheet4" for H.

the range for the cloumns i have set to whatever the last row used is.

So i need to have a variable for my sheets so i cna assign them, not to sure how to get around that.

LR2 = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row
LR1 = Target.Row
Sheets("Sheet1").Range("A" & LR1 & ":" & "D" & LR1).Copy Destination:=Sheets("sheet2").Range("A" & LR2 + 1)

right now i have actual sheets in there just to make sure that code works. but i need that "sheet2" to become some kinda variable to point to other sheets.


thanks in advance

BTSP

 

[iggydarsa]

Re: To use Coloumns to Past to Sheets

I am not quite sure how you are handling if the user clicks on column I or higher but this code might give you an idea

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column > 5 And Target.Column - 4 <= Sheets.Count Then
        LR2 = Sheets("sheet" & Target.Column - 4).Range("A" & Rows.Count).End(xlUp).Row
        LR1 = Target.Row
        Sheets("Sheet1").Range("A" & LR1 & ":" & "D" & LR1).Copy _
            Destination:=Sheets("sheet" & Target.Column - 4).Range("A" & LR2 + 1)
    End If
End Sub

 

[btsp]

Re: To use Coloumns to Past to Sheets

hmm well, my sheets im trying to copy to, are

sheet2= HONI
sheet3= IESO
sheet4= HMI


im not sure about that code, could you explain it?


i need something like

If a cell in column "F" was selected than Variable= "HONI"
If a cell in column "G" was selected than Variable= "IESO"
If a cell in column "H" was selected than Variable= "HMI"

than for this line

LR2 = Sheets(Variable).Range("A" & Rows.Count).End(xlUp).Row '
 
'Targets the row that you are clicking so it know what to copy
LR1 = Target.Row
 
'takes the range you want to copy and pastes it into the proper sheet
Sheets("Sheet1").Range("A" & LR1 & ":" & "D" & LR1).Copy Destination:=Sheets(Variable).Range("A" & LR2 + 1)

just thorw in teh variable into the sheets.

i know it doesnt work liek that ...but thats what i am thinking i need to do.

 

[iggydarsa]

Re: To use Coloumns to Past to Sheets

I thought you were using the names Sheet2, sheet3 etc

try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column >= 6 And Target.Column <= 8 Then
    Select Case Target.Column
        Case 6
            shName = "HONI"
        Case 7
            shName = "IESO"
        Case 8
            shName = "HMI"
    End Select
LR2 = Sheets(shName).Range("A" & Rows.Count).End(xlUp).Row '
 
'Targets the row that you are clicking so it know what to copy
LR1 = Target.Row
 
'takes the range you want to copy and pastes it into the proper sheet
Sheets("Sheet1").Range("A" & LR1 & ":" & "D" & LR1).Copy Destination:=Sheets(shName).Range("A" & LR2 + 1)
End If
End Sub

 

[btsp]

Re: To use Coloumns to Past to Sheets

that is perfect thank you ver much worked like a charm

Btsp