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]

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

 

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