Adding data from other worksheets into one [Closed]

Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello,
I am having aproblem loading a bunch of data from other worksheets into one worksheet (sheet1) in Excel. I have been trying to use the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Columns = 6 Then
If Len(Trim(Target.Value)) <> 0 Then
Target.EntireRow.Copy Worksheets("Master").Cells( _
Rows.Count, "A").End(xlUp)(2)
End If
End If

End Sub

But I can't get Target.Columns to equal all columns from A to T.
I also want to delete the old line in sheet1 if the data was previously wirtten to this master sheet. I set it to update whenever someone presses enter after having entered data in a specific column (as said before I am hoping to get it working so that all columns will copy when data is changed and enter is hit) but I only want it to delete data in the master if the data was copied from the same place in the worksheets before.

Can someone please help me with this?

5 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Target.Column will give you the column inwhich the change has occured.

to get all used columns in a sheet use ActiveSheet.UsedRange.Columns.Count
Do you have any idea how I would identify the line that this was previously copied from in order to delete it on the Master?
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
"this was previously copied " - can you explain further
Sorry, I meant that if a line was already copied to the master once is there any way to identify it when data it again. What I wanted to do was delete the previously copied line in Master (not the line in original worksheet that the data was from) and insert the newly changed data where the old data was.

I tried doing this:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If ActiveSheet.UsedRange.Columns.Count Then
If Len(Trim(Target.Value)) <> 0 Then
Target.EntireRow.Copy Worksheets("Master").Cells( _
Rows.Count, "A").End(xlUp)(2)
Target.EntireRow.Delete <-
End If
End If

End Sub

But that only deletes the line in the original worksheet. I don't even know if it's possible to do what I want here, but thanks for your help so far.
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
What is particular in the data you copied
Then go through all the rows to check for that content then delete that 'entirerow'

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!