You are here:

Excel/Copy data of sheet1 to sheet2

Advertisement


Question
Image
Image  
QUESTION: Dear Damon,
On sheet1 i enter name in col.B and amount in col.F. Please provide code to copy name to sheet2 col.D corresponding to row containing 'Name' and amount corresponding to row containing 'Tax amount'. However:

1. If the cell is already filled then search for next blank cell and fill the name and tax amount accordingly.

2. When no name and tax amount is found in sheet2 for the data entered at sheet1, give error message "Form need to be Added"

ANSWER: Hi Nabam,

Here is a macro that I believe does what you describe:

Sub XferData()
  'Transfers name and amount data from Sheet1 to Sheet2
  
  Dim Name    As String
  Dim Amount  As Single      'Tax amount
  Dim Dcol    As Integer     'Sheet2 destination column
  Dim Lrow    As Long        'Sheet1 last data-filled row
  Dim Nrow    As Long        'Sheet2 row containing "Name"
  Dim NTA     As Long        'Sheet2 row containing "Tax amount"
  Dim Sh1     As Worksheet
  Dim Sh2     As Worksheet
  Dim C       As Range

  Set Sh1 = Worksheets("Sheet1")
  Set Sh2 = Worksheets("Sheet2")
  
  'Find last row on Sheet1
  Lrow = Sh1.Range("B65536").End(xlUp).Row
  
  Name = Sh1.Cells(Lrow, "B")
  Amount = Sh1.Cells(Lrow, "F")
  
  'Find "Name" in Sheet2
  Set C = Sh2.Range("A1:Z100").Find("Name", Range("A1"))
  
  If C Is Nothing Then GoTo NeedForm
  
  Dcol = C.Offset(0, 100).End(xlToLeft).Column + 1
  Nrow = C.Row
  
  Set C = Sh2.Range("A1:Z100").Find("Tax amount", C)
  If C Is Nothing Then GoTo NeedForm
  NTA = C.Row
  
  'write results to Sheet2
  Sh2.Cells(Nrow, Dcol) = Name
  Sh2.Cells(NTA, Dcol) = Amount

  Exit Sub
     
NeedForm:
  MsgBox "Form needs to be added", vbCritical, "Form or data not found"
  
End Sub



My assumption is that you would run this macro each time you add a row of data to Sheet1.

If I have misinterpreted your question or missed the mark in any way please follow up with explanation or clarification.

Damon

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

QUESTION: The name and amount of sheet1 has to be copied to sheet2 Col. D only. The Name and Tax amount in sheet2 appear in Col.B only. i.e there are lots of 'Name' and 'Tax amount' in col.B of sheet2. e.g.


Sheet2 col.B

Name:
xxxx
xxxx
Tax amount
xxxx


Name:
xxxx
xxxx
Tax amount
xxxx


Name:
xxxx
xxxx
Tax amount
xxxx

ANSWER: Hi again Nabam,

Okay, here is another go at your problem.  Fortunately it only required a few minor changes to the code from before.  I assumed based on what you said that the name and tax amount values should always be written to column C of Sheet2.

Sub XferData()
  'Transfers name and amount data from Sheet1 to Sheet2
  
  Dim Name    As String
  Dim Amount  As Single      'Tax amount
  Dim Lrow    As Long        'Sheet1 last data-filled row
  Dim Nrow    As Long        'Sheet2 row containing "Name"
  Dim NTA     As Long        'Sheet2 row containing "Tax amount"
  Dim Sh1     As Worksheet
  Dim Sh2     As Worksheet
  Dim C       As Range

  Set Sh1 = Worksheets("Sheet1")
  Set Sh2 = Worksheets("Sheet2")
  
  'Find last row on Sheet1
  Lrow = Sh1.Range("B65536").End(xlUp).Row
  
  Name = Sh1.Cells(Lrow, "B")
  Amount = Sh1.Cells(Lrow, "F")
  
  'Find "Name" in Sheet2
  Set C = Sh2.Range("B1:B500").Find("Name", Range("B1"))
  
  If C Is Nothing Then GoTo NeedForm
  
  Do Until IsEmpty(C.Offset(0, 1))
     Set C = Sh2.Range("B1:B500").FindNext(C)
     If C Is Nothing Then GoTo NeedForm
  Loop
  
  Nrow = C.Row
  
  Set C = Sh2.Range("B1:B500").Find("Tax amount", C)
  If C Is Nothing Then GoTo NeedForm
  NTA = C.Row
  
  'write results to Sheet2
  Sh2.Cells(Nrow, "C") = Name
  Sh2.Cells(NTA, "C") = Amount

  Exit Sub
     
NeedForm:
  MsgBox "Form needs to be added", vbCritical, "Form or data not found"
  
End Sub


Again, let me know of any problems.

Damon

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

JPEG file
JPEG file  
QUESTION: Sir, it seems i couldn't clarify my question to you. So, i attach herewith an image alongside for your easy understanding of my question. Please see the image.

Answer
Hi again Nabam,

Sorry about misinterpreting your question. The picture you attached is exactly as I had assumed all along, and it worked for me with the data in that form, so it seems there are two possible reasons for it not working for you.

1. I assumed that you added one name at a time to Sheet1 and each time you add a name you run my macro to add the data to Sheet2.  I'm now thinking that you intended that the macro to add all the data from Sheet1 to Sheet2.  So here's the code for that:

Sub XferData()
  'Transfers name and amount data from Sheet1 to Sheet2
  
  Dim Name    As String
  Dim Amount  As Single      'Tax amount
  Dim iRow    As Long        'Row number on sheet1
  Dim Lrow    As Long        'Sheet1 last data-filled row
  Dim Nrow    As Long        'Sheet2 row containing "Name"
  Dim NTA     As Long        'Sheet2 row containing "Tax amount"
  Dim Sh1     As Worksheet
  Dim Sh2     As Worksheet
  Dim C       As Range
  Dim Clast   As Range

  Set Sh1 = Worksheets("Sheet1")
  Set Sh2 = Worksheets("Sheet2")
  
  'Find last row on Sheet1
  Lrow = Sh1.Range("B65536").End(xlUp).Row
  
  'loop through all names in Sheet1
  Set C = Sh2.Range("B1")
  Set Clast = C
  
  For iRow = 2 To Lrow
  
     Name = Sh1.Cells(iRow, "B")
     Amount = Sh1.Cells(iRow, "F")
         
     'Find "Name" in Sheet2
     Set C = Sh2.Range("B1:B500").Find("Name", C)
     
     If C.Row <= Clast.Row Then GoTo NeedForm
     
     Do Until IsEmpty(C.Offset(0, 1))
        Set C = Sh2.Range("B1:B500").FindNext(C)
        If C.Row < Clast.Row Then GoTo NeedForm
        Set Clast = C
     Loop
     
     Nrow = C.Row
     
     Set C = Sh2.Range("B1:B500").Find("Tax amount", C)
     If C < Clast Then GoTo NeedForm
     NTA = C.Row
     
     'write results to Sheet2
     Sh2.Cells(Nrow, "C") = Name
     Sh2.Cells(NTA, "C") = Amount
  
  Next iRow

  Exit Sub
     
NeedForm:
  MsgBox "Form needs to be added", vbCritical, "Form or data not found"
  
End Sub

2. I made a mistake in the last version that would cause it to work only if Sheet2 was the active worksheet when the macro is run.  If my original assumption regarding processing only the last name in Sheet1 was correct, then this could be why it didn't work.  You only have to change

Set C = Sh2.Range("B1:B500").Find("Name", Range("B1"))

to

Set C = Sh2.Range("B1:B500").Find("Name", Sh2.Range("B1"))

to fix this problem.  Then either sheet can be active when you run the macro.

Please let me know if one of these solves the problem.

Damon

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


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.