Updating Other sheets

btsp

Board Regular
Joined
Dec 8, 2008
Messages
102
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

Try something like this

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

btsp

Board Regular
Joined
Dec 8, 2008
Messages
102
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..
 

btsp

Board Regular
Joined
Dec 8, 2008
Messages
102
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.


Code:
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

Board Regular
Joined
Dec 8, 2008
Messages
102
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

Board Regular
Joined
Dec 8, 2008
Messages
102
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
 

Forum statistics

Threads
1,171,144
Messages
5,874,034
Members
433,017
Latest member
inxcndd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top