adding data from other worksheets into oneClosed

Question

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?

Excelguru · Answer

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

gabe · Answer

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?

Excelguru · Answer

"this was previously copied " - can you explain further

gabe · Answer

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.

Excelguru · Answer

What is particular in the data you copied
Then go through all the rows to check for that content then delete that 'entirerow'

Adding data from other worksheets into one

5 responses

Similar discussions

Subscribe To Our Newsletter!