Hi Mr. excels,
I am quite new to VBA, and every week I receive an excel file that contains all the data of our sales which needs to be reconcile, I would like to have a VBA code that extract all the data from the sales file to a new workbook. I did find a VBA that suits more or less to my requirement, but I can't seem to get it to work. I hope that all you excel legends here can assist me in modifying the code.
<tbody>
</tbody>
I would be really grateful if you could help!
best regards,
M
I am quite new to VBA, and every week I receive an excel file that contains all the data of our sales which needs to be reconcile, I would like to have a VBA code that extract all the data from the sales file to a new workbook. I did find a VBA that suits more or less to my requirement, but I can't seem to get it to work. I hope that all you excel legends here can assist me in modifying the code.
Code:
[COLOR=#000000][FONT=-webkit-standard]Option Explicit[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]Sub findData()[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'Let's define the variables[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] Dim GCell As Range[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] Dim Txt$, MyPath$, MyWB$, MySheet$[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] Dim myValue As Integer[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'Search what[/FONT][/COLOR]
[COLOR=#ff0000][FONT=-webkit-standard]Txt = InputBox("What do you want to search for?")
[/FONT][/COLOR]
can I get rid of this part by extract all the data instead of asking what I want to search for?
[COLOR=#000000][FONT=-webkit-standard] 'The path to the workbook to be searched[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] MyPath = "C:\raw-data"[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'The name of the workbook to be searched[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] MyWB = "data.xlsx"[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'Use the current sheet to store the found data[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] MySheet = ActiveSheet.Name[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'use error handling routine in case of errors[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] On Error GoTo ErrorHandler[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'Turn off screen updating to run macro faster[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] Workbooks.Open Filename:=MyPath & MyWB[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'Search for the specified data[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] Set GCell = ActiveSheet.Cells.Find(Txt)[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'Record values in current workbook[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] With ThisWorkbook.ActiveSheet.Range("A1")[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] .Value = "SN"[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] .Offset(0, 1).Value = "month"[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] .Offset(1, 0).Value = GCell.Value[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] myValue = GCell.Offset(0, 1).Value[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] If myValue >= 6 Then[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] .Offset(1, 1).Value = GCell.Offset(0, 1).Value[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] End If[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] .Columns.AutoFit[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] .Offset(1, 1).Columns.AutoFit[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] End With[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] 'Close data workbook; don't save it; turn screen updating back on[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] ActiveWorkbook.Close savechanges:=False[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard] Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#000000][FONT=-webkit-standard]Exit Sub[/FONT][/COLOR]
SN | Month | Invoice type | Invoice No. | Supplier | Description | Amount | VAT | VAT | Amount | Invoice | Due Date | FX rate | outstandings | Position | DSO | Sales In € | Cost center |
gross | % | amount | net | Date | |||||||||||||
768890 | Dec-16 | sales Invoice | 85MC980999 | AAA | registratioin_fee_SN768890 | $ 4000 ,00 | 0% | $ - | $ 4000 ,00 | 30/12/2014 | 30/12/2014 | $ 1,0541 | $ - | Dec-14 | 3600 | ABD | |
UIJIOP | Jan-16 | sales Invoice | ACDC098789 | BBB | registratioin_fee_SNUIJIOP | $ 4000 ,01 | 0% | $ - | $ 4000 ,01 | 05/01/2014 | 18/01/2014 | $ 1,0746 | $ - | Jan-15 | 3600 | acc | |
8782JK | Jan-16 | sales Invoice | 16AC099887 | CCC | process_fee_SN8782jk | $ 4000 ,02 | 0% | $ - | $ 4000 ,02 | 15/01/2014 | 25/01/2014 | $ 1,0914 | $ - | Feb-15 | 3600 | 9990 | |
9898JK | Jan-16 | sales Invoice | DGHN787890 | DDD | General Service Insp. SN9898JK | $ 4000 ,03 | 0% | $ - | $ 4000 ,03 | 18/01/2014 | 17/02/2014 | $ 1,0892 | $ - | Mar-15 | 3600 | 78789 | |
9898HJ | Jan-16 | sales Invoice | 17MIKIOLK | AAD | process_fee_SN9898HJ | $ 110.000,00 | 0% | $ - | $ 110.000,00 | 08/01/2015 | 18/01/2015 | $ 1,0861 | $ - | Apr-15 | 3600 | 1111 |
<tbody>
</tbody>
I would be really grateful if you could help!
best regards,
M
Last edited by a moderator: