You are here:

# Excel/Copy data of sheet1 to sheet2

Question

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"

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:

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

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:

End Sub

Again, let me know of any problems.

Damon

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

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.

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:

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

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

#### 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.