Automate extraction of data VBA issues

[Aberdham]

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.

[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]

[TABLE="width: 2122"]
<tbody>[TR]
[TD]SN[/TD]
[TD]Month[/TD]
[TD]Invoice type[/TD]
[TD]Invoice No.[/TD]
[TD]Supplier[/TD]
[TD]Description[/TD]
[TD]Amount[/TD]
[TD]VAT[/TD]
[TD]VAT[/TD]
[TD]Amount[/TD]
[TD]Invoice[/TD]
[TD]Due Date[/TD]
[TD]FX rate[/TD]
[TD]outstandings[/TD]
[TD]Position[/TD]
[TD]DSO[/TD]
[TD]Sales In €[/TD]
[TD]Cost center[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]gross[/TD]
[TD]%[/TD]
[TD]amount[/TD]
[TD]net[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: right"]768890[/TD]
[TD="align: right"]Dec-16[/TD]
[TD]sales Invoice[/TD]
[TD]85MC980999[/TD]
[TD]AAA[/TD]
[TD]registratioin_fee_SN768890[/TD]
[TD]$ 4000 ,00[/TD]
[TD="align: right"]0%[/TD]
[TD]$ -[/TD]
[TD]$ 4000 ,00[/TD]
[TD="align: right"]30/12/2014[/TD]
[TD="align: right"]30/12/2014[/TD]
[TD]$ 1,0541[/TD]
[TD]$ -[/TD]
[TD="align: right"]Dec-14[/TD]
[TD][/TD]
[TD="align: right"]3600[/TD]
[TD]ABD[/TD]
[/TR]
[TR]
[TD]UIJIOP[/TD]
[TD="align: right"]Jan-16[/TD]
[TD]sales Invoice[/TD]
[TD]ACDC098789[/TD]
[TD]BBB[/TD]
[TD]registratioin_fee_SNUIJIOP[/TD]
[TD]$ 4000 ,01[/TD]
[TD="align: right"]0%[/TD]
[TD]$ -[/TD]
[TD]$ 4000 ,01[/TD]
[TD="align: right"]05/01/2014[/TD]
[TD="align: right"]18/01/2014[/TD]
[TD]$ 1,0746[/TD]
[TD]$ -[/TD]
[TD="align: right"]Jan-15[/TD]
[TD][/TD]
[TD="align: right"]3600[/TD]
[TD]acc[/TD]
[/TR]
[TR]
[TD]8782JK[/TD]
[TD="align: right"]Jan-16[/TD]
[TD]sales Invoice[/TD]
[TD]16AC099887[/TD]
[TD]CCC[/TD]
[TD]process_fee_SN8782jk[/TD]
[TD]$ 4000 ,02[/TD]
[TD="align: right"]0%[/TD]
[TD]$ -[/TD]
[TD]$ 4000 ,02[/TD]
[TD="align: right"]15/01/2014[/TD]
[TD="align: right"]25/01/2014[/TD]
[TD]$ 1,0914[/TD]
[TD]$ -[/TD]
[TD="align: right"]Feb-15[/TD]
[TD][/TD]
[TD="align: right"]3600[/TD]
[TD="align: right"]9990[/TD]
[/TR]
[TR]
[TD]9898JK[/TD]
[TD="align: right"]Jan-16[/TD]
[TD]sales Invoice[/TD]
[TD]DGHN787890[/TD]
[TD]DDD[/TD]
[TD]General Service Insp. SN9898JK[/TD]
[TD]$ 4000 ,03[/TD]
[TD="align: right"]0%[/TD]
[TD]$ -[/TD]
[TD]$ 4000 ,03[/TD]
[TD="align: right"]18/01/2014[/TD]
[TD="align: right"]17/02/2014[/TD]
[TD]$ 1,0892[/TD]
[TD]$ -[/TD]
[TD="align: right"]Mar-15[/TD]
[TD][/TD]
[TD="align: right"]3600[/TD]
[TD="align: right"]78789[/TD]
[/TR]
[TR]
[TD]9898HJ[/TD]
[TD="align: right"]Jan-16[/TD]
[TD]sales Invoice[/TD]
[TD]17MIKIOLK[/TD]
[TD]AAD[/TD]
[TD]process_fee_SN9898HJ[/TD]
[TD]$ 110.000,00[/TD]
[TD="align: right"]0%[/TD]
[TD]$ -[/TD]
[TD] $ 110.000,00[/TD]
[TD="align: right"]08/01/2015[/TD]
[TD="align: right"]18/01/2015[/TD]
[TD]$ 1,0861[/TD]
[TD]$ -[/TD]
[TD="align: right"]Apr-15[/TD]
[TD][/TD]
[TD="align: right"]3600[/TD]
[TD="align: right"]1111[/TD]
[/TR]
</tbody>[/TABLE]

I would be really grateful if you could help!

best regards,
M

 

[Aberdham]

The duplicate columns are created by the macro. It uses Advanced Filter to get the list of Machinery. It is necessary to create it, but it is just temporary, so it is possible to delete after the result is copied. The code I provided was just a proof of concept - we can refine it if necessary, and add other things like event-driven actions.

That sounds great! Thank you! I do have a question about how do i modify the code when the Name of the sheet Changes (e.g sales -- sales costs ; overview -- overview machinery 1)
I did try to modify the code, but each time i get a run time error 9
by Event-driven, do you mean each time a single entry are recorded, it will be directly Transfer to the respective sheet?


So are you saying you want to create a new sheet for each Machinery listed? Do they already exist, or do you want to create a new one? If one already exists, do you want to overwrite it?

I would like to create 75 new Sheets -- each represents a single overview for the respective engine. I am currently working on spliting the data into the 3 Output Sheets. is that doable?

 

[Aberdham]

https://1drv.ms/x/s!Aqt4VfikFsyKbXrH7TJdaSfLee8
this should be the correct link.

thanks

Hey Iliace,

I believe my latest post has similar mechanism like this one... only by extracting certain columns. could you also take a look at that and lend me a helping Hand?

thank you so much !!