1099 USA tax form

[Sharid]

Hi

I’m trying to build a spreadsheet 1099 tax form for USA tax purposes, so I can complete the form for all the vendors via a marco/s.


On Sheet1 (Customer Record) I have the customer details Tax Numbers to zip code. This will be done manually as they supply W9 forms.


Zip Code (Post code) is in column G

On Sheet2 (Input Data) there will be the vendors data, this will be imported from a WordPress plugin. The plugin will produce a CVS file which can open in excel. The plugin data will NOT hold the Vendors TAX Number (National insurance number). This data I will have already input manually into sheet1 .


I will copy the plugin data into Sheet2 (Input data).

I need a macro that will Do the following.

1. Look for the zip code in Sheet1 Column G and
2. Then find the same in Sheet2 Column G
3. Copy data from Sheet2 Column G range G:AB and paste it into sheet1 at the right location, then move to next record.

 

[alansidman]

try this:

Option Explicit
Sub Sharid()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lr As Long, lr2 As Long, i As Long, j As Long
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        For j = 1 To lr2
            If s2.Range("G" & j) = s1.Range("G" & i) Then
                s2.Range("G" & j & ":AB" & j).Copy
                s1.Range("H" & i).PasteSpecial xlPasteValues
            End If
        Next j
    Next i


End Sub

 

[Sharid]

Thanks Alan

I have this code for Alan, which does most of what i want, I have just made a minor change to it, to insert and delete a column.

The code matches the data in column G sheet1 to Data in Sheet2 Column G and copy and pastes it into sheet1, this part is fine.
The problem I'm having is that it also replaces the headings in sheet1.

I need it so start from row3, so

It check sheet1 Row3 for the data
Matches it in sheet2 starting at row3
and then pastes it into sheet1 starting at row3.

Like I said this code works fine apart from it changes the headings that are in row2 on sheet1

Private Sub CommandButton1_Click()
Range("H1").EntireColumn.Insert

    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Customer Record")
    Set s2 = Sheets("Input Data")
    Dim lr As Long, lr2 As Long, i As Long, j As Long
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        For j = 1 To lr2
            If s2.Range("G" & j) = s1.Range("G" & i) Then
                s2.Range("G" & j & ":AB" & j).Copy
                s1.Range("H" & i).PasteSpecial xlPasteValues
            End If
        Next j
    Next i
Range("H1").EntireColumn.Delete


End Sub

Also it paste data into row1 on sheet1 that can not be matched, this is normally the last date in the row on sheet2 it cannot match.

Example: if on sheet2 row66 column G it can't find "123456" which is in Sheet1 then it will copy this data in to sheet1 row1, which is blank only has command button.

Thanks to ALan for helping on this one, code does work,

 

[Sharid]

Can anyone help on this one, i'm really stuck

Thanks

 

[alansidman]

If I am understanding your request correctly

Option Explicit


Private Sub CommandButton1_Click()
Range("H1").EntireColumn.Insert


    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Customer Record")
    Set s2 = Sheets("Input Data")
    Dim lr As Long, lr2 As Long, i As Long, j As Long
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("G" & Rows.Count).End(xlUp).Row
    For i = 3 To lr
        For j = 3 To lr2
            If s2.Range("G" & j) = s1.Range("G" & i) Then
                s2.Range("G" & j & ":AB" & j).Copy
                s1.Range("H" & i).PasteSpecial xlPasteValues
            End If
        Next j
    Next i
Range("H1").EntireColumn.Delete




End Sub

 

[mikerickson]

From the OP, it sounds like a VLOOKUP formula is what is needed.

 

[alansidman]

Mike
That was my first thought, but the OP is looking to populate columns G:AB and that seemed to me like a lot of Vlookups. Are you seeing something different?
Alan

 

[Sharid]

Here is a link for the download file https://app.box.com/s/zjo7063j5485j3mmiq1op4tags54chx8

Button: Match Data, Alan'c Code and a bit from me.

1) My bit highlights rows in YELLOW where cells in column P are empty
2) Deletes imported Column H as it created 2 data sets

Alan's code matches data from Customer Record Column G with Input data Column G and imports its into Customer Record Sheet. Any records that are not matched, the rows are left blank.

1)Here is the problem, the last row that is not matched appears in row1 of Customer record, this is shown in green and red for your reff.

2) Headings will be overwritten in first sheet as it copies headings from second sheet. Currently this is not showing as both sheets have the same headings.

Button: Clear color, clears the color Yellow

Button: Clear Data, Clears data from H:AB

Hope this helps

 

[mikerickson]

Mike
That was my first thought, but the OP is looking to populate columns G:AB and that seemed to me like a lot of Vlookups. Are you seeing something different?
Alan

I was thinking that a bulk "write VLOOKUP formula" and "value=value" would be quicker than a loop of .Finds.

 

[Sharid]

Thanks Alan

You fixed this yesterday, I did not realise that you had made a small change to your original code, hence my post with the download link.

Thanks again