Updating Other sheets

[btsp]

Okay,
Say I have 4 coloumns of parts and 100's of rows for those coloumns.

Than i have 3 seperate coloumns that i have coded that when clocked a check mark appears:

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F8:F16")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If

After these selections are checked i want them to copy that row and put them onto another sheet.

i could make 100's of macors, but i was hoping i could use the ActiveCell as a variable or something for the range to copy.

if this makes sense

btsp

 

[VoG]

Hello and welcome to MrExcel.

Try something like this

Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1")

 

[btsp]

thank you for the start.

I need the range to a be a variable for the second page, so each checkmark i select it will add one more to the A1.

i cant seem to find it on here.

but than when the check mark is uncheckd i want that same line to be delted...but if it keeps counting up than im going to be deleting different lines..hmmm..

 

[VoG]

To append to another sheet try

Dim LR As Long
LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & LR + 1)

As an example of deleting from the second sheet see: http://www.mrexcel.com/forum/showthread.php?t=348858

 

[btsp]

Okay sorry i havent used VB in quite sometime and im really rusty but could someone explain this coding to me , im not to sure about the Finding and Found, but would be most appreciated.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const FirstRow As Long = 10 'change 10 to the first row to start on
Dim LR As Long, ToFind As Variant, Found As Range, OtherSheet As String
Dim LR2 As Long
LR2 = Sheets("Report").Range("B" & Rows.Count).End(xlUp).Row
If Target.Count > 1 Then Exit Sub
If Target.Value = "TALL" Or Target.Value = "SHORT" Then
Application.EnableEvents = False
With Sheets(Target.Value)
LR = WorksheetFunction.Max(.Range("A" & Rows.Count).End(xlUp).Row + 1, FirstRow)
ToFind = Sh.Range("C" & Target.Row)
Target.EntireRow.Copy Destination:=.Range("A" & LR)
Range("C" & Target.Row).Copy Destination:=Sheets("Report").Range("B" & LR2 + 1)
End With
If Target.Value = "TALL" Then
OtherSheet = "SHORT"
Else
OtherSheet = "TALL"
End If
Set Found = Sheets(OtherSheet).Columns("C").Find(what:=ToFind)
If Not Found Is Nothing Then Found.EntireRow.Delete
Application.EnableEvents = True
End If
End Sub
[\code]
 
 
thanks in advance

 

[btsp]

ya, im trying to use the To find and Found on my code, but not working.


Dim LR As Long, ToFind As Variant, Found As Range, OtherSheet As String

'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("F8:F16")) Is Nothing Then Exit Sub
'set Target font tp "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Selection.Offset(0, 3).Select

Application.EnableEvents = False
With Sheets(Target.Value)

LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
ToFind = Sh.Range("A" & Target.Row)
Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & LR + 1)
End With



End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Cancel = True
Selection.Offset(0, 3).Select

Set Found = Sheets("Sheet2").Columns("A").Find(what:=ToFind)
If Not Found Is Nothing Then Found.EntireRow.Delete
Application.EnableEvents = True


Exit Sub
End If


any suggestions

 

[btsp]

hah well i figured it out what i did wrong ,now i just need to copy from A-D in the rows not the entire row, and also when i delete it isnt the entire row as well.

Btsp