You are here:

Excel/Excel Copying Information

Advertisement


Question
QUESTION: Hi,

I have been using the code below to copy information from one sheet to another in a workbook.  When information is entered into coloumn 9 the whole row is exported to the another sheet ('Mentoring') and pasted in the next available row.

However, at present for some reason it will only add to the second row of the new spreadsheet under the heading and with every new entry just overwrites the previous one rather than going down to the next row and entering the information there.

Help.....

Thank you very much in advance,

Rosie


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 9 Then
If Len(Trim(Target.Value)) <> 0 Then
Target.EntireRow.Copy Worksheets("Mentoring").Cells( _
  Rows.Count, "A").End(xlUp)(2)
End If
End If
End Sub

ANSWER: Perhaps this solves the matter:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 9 Then
If Len(Trim(Target.Value)) <> 0 Then
Target.EntireRow.Copy Worksheets("Mentoring").Cells( _
  Worksheets("Mentoring").Rows.Count, 1).End(xlUp).Offset(2)
End If
End If
End Sub

---------- FOLLOW-UP ----------

Tab
Tab  
Main Sheet
Main Sheet  
QUESTION: Hi,

I'm afraid that hasn't worked.

I have attached a picture of the main sheet for you to see, the idea is that when someone selects yes to 'Mentoring' 'Befriending' or 'Onside Peer Network' then the whole row will be replicated the corresponding tab.  Also attached is an example of one the tabs using the code that you gave me, as you can see its not inserting the 3 entries from the main sheet #I have hidden some of the cells as they are not required#

Any ideas?

Thank you again for you help, it is greatly appreciated.

Rosie

ANSWER: Odd. Lets re-write the routine a bit then:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 9 Then
If Len(Trim(Target.Value)) <> 0 Then
With Worksheets("Mentoring")
Target.EntireRow.Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1)
End If
End If
End Sub

Note that column A does have to contain a value, otherwise we're overwriting the previous entry. Maybe that is the root cause?

---------- FOLLOW-UP ----------

Error
Error  
QUESTION: Still no joy, the attached is what I am seeing.....

Answer
Duh, of course, I did not test it...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 9 Then
If Len(Trim(Target.Value)) <> 0 Then
With Worksheets("Mentoring")
Target.EntireRow.Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1)
end With
End If
End If
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.