VBA code for automatically data transferring

[Aberdham]

Hello All:

I have a question regarding how to automatically transferring data from one excel workbook to another one. I have 2 workbooks, one of them saves all the trans-action that we have made over the last 5 years. the workbook contains 20 columns, including invoice numbers, invoice amount, customers etc and more than 2500 en-tries(rows)


The master file is updated everyday with new entries. and I would like to transfer the data from the master file to the posting file without doing copy pasting. Since the data is used for reporting, certain columns are not necessary. so I would like to transfer a selection of data if possible. Can anyone help me with writing a VBA code for this?


Best regards,

 

[My Aswer Is This]

WE would need specific details.
Like take this range and put it where.

And how would you suggest doing this without a vba script using Copy and pasting.

Why do you object to copy/paste

 

[Aberdham]

For example: the master file has columns as the following: Month, customer, customer region, invoice number, invoice amount, Invoice date, due date, product description etc.
normally, we would take the raw data daily from a website and do a lot of manually copy/paste. that's rather time-consuming and creates a lot of errors. my idea now is to transfer the take the raw data and run a VBA macro to automatically transfer the data from the master file to the posting file. so that it saves a lot of time and creates much less errors.

Thank you.
Look forward to hear from you soon!

 

[My Aswer Is This]

Sure manually copying lots of data from one location to another is a lot of work. But a lot of things Vba scripts do is copy paste.

So you need to explain in details what data you want copied and where you want it pasted.

For example:

Copy Workbook Me.xlsm.Sheets("Master") Column A to D and Paste them to

Workbook You.xlsm.Sheets("Master"). columns A to D

We need column numbers like 1 or 2 Or columns A and D

Do not say Columns Date to columns Company

See it's easy to know what column A is
But its not easy to know what column Dates or Names is.

 

[My Aswer Is This]

I asked for you tp provide more details in my previous post but you never gave more specific details.

 

[Aberdham]

I asked for you tp provide more details in my previous post but you never gave more specific details.

sorry for the delay .......

so basically I have 3 sheets with related info about a machine:
there are altogether 120 of them.

sheet 1 (historical cost) would look like:
[TABLE="width: 1327"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Invoice type[/TD]
[TD]Invoice Number[/TD]
[TD]Supplier/Debitor[/TD]
[TD]Description[/TD]
[TD]Invoice Date[/TD]
[TD]FX rate[/TD]
[TD]USD Amount[/TD]
[TD]EUR Amount[/TD]
[TD]Change in Inventory[/TD]
[TD]Machinery[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]K8554214[/TD]
[TD]ADA[/TD]
[TD]Deposit SN 844451[/TD]
[TD="align: right"]01/12/2017[/TD]
[TD="align: right"]1,1885[/TD]
[TD]$361.067,54[/TD]
[TD="align: right"]303.801,05 €[/TD]
[TD="align: right"]-303.801,05 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]K8554215[/TD]
[TD]ADA[/TD]
[TD]final payment_ESN 848462[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]1,1993[/TD]
[TD]$358.718,75[/TD]
[TD="align: right"]299.106,77 €[/TD]
[TD="align: right"]-299.106,77 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]K8554216[/TD]
[TD]APOM[/TD]
[TD]final payment_ESN 848462[/TD]
[TD="align: right"]02/02/2018[/TD]
[TD="align: right"]1,2492[/TD]
[TD]$ 2.600,60[/TD]
[TD="align: right"]2.081,82 €[/TD]
[TD="align: right"]-2.081,82 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]85426589[/TD]
[TD]APOM[/TD]
[TD]inspection[/TD]
[TD="align: right"]02/02/2018[/TD]
[TD="align: right"]1,2492[/TD]
[TD]$ 3.461,33[/TD]
[TD="align: right"]2.770,84 €[/TD]
[TD="align: right"]-2.770,84 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]85426589[/TD]
[TD]UIJ[/TD]
[TD]opmen[/TD]
[TD="align: right"]02/02/2018[/TD]
[TD="align: right"]1,2492[/TD]
[TD]$ 18.988,94[/TD]
[TD="align: right"]15.200,88 €[/TD]
[TD="align: right"]-15.200,88 €[/TD]
[TD="align: right"]1[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 (sales)

[TABLE="width: 1699"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Type of invoice[/TD]
[TD]Invoice Number[/TD]
[TD]Supplier/Debitor[/TD]
[TD]Description[/TD]
[TD]Invoice Date[/TD]
[TD]FX rate[/TD]
[TD]USD Amount[/TD]
[TD]EUR Amount[/TD]
[TD]Change in Inventory[/TD]
[TD]Machinery[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]AR00214522[/TD]
[TD]ADA[/TD]
[TD]AR00251452[/TD]
[TD="align: right"]11/04/2018[/TD]
[TD="align: right"]1,2384[/TD]
[TD]$ 15.222,00[/TD]
[TD="align: right"]€12.291,67[/TD]
[TD="align: right"]-12.291,67 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

sheet 3( purchase)

[TABLE="width: 1699"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Type of invoice[/TD]
[TD]Invoice Number[/TD]
[TD]Supplier/Debitor[/TD]
[TD]Description[/TD]
[TD]Invoice Date[/TD]
[TD]FX rate[/TD]
[TD]USD Amount[/TD]
[TD]EUR Amount[/TD]
[TD]Change in Inventory[/TD]
[TD]Machinery[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]58485[/TD]
[TD]AAD[/TD]
[TD]AP001523[/TD]
[TD="align: right"]11/04/2018[/TD]
[TD="align: right"]1,2384[/TD]
[TD]$ 15.222,00[/TD]
[TD="align: right"]€12.291,67[/TD]
[TD="align: right"]12.291,67 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]584885[/TD]
[TD]AAR[/TD]
[TD]AP001524[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1,2321[/TD]
[TD]$ 1.600,00[/TD]
[TD="align: right"]€1.298,60[/TD]
[TD="align: right"]1.298,60 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]584882[/TD]
[TD]AAE[/TD]
[TD]AP001525[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1,2321[/TD]
[TD]$ 500,00[/TD]
[TD="align: right"]€405,81[/TD]
[TD="align: right"]405,81 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]48595[/TD]
[TD]AES[/TD]
[TD]AP001526[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1,2321[/TD]
[TD]$ 18.455,00[/TD]
[TD="align: right"]€14.978,49[/TD]
[TD="align: right"]14.978,49 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]485953[/TD]
[TD]AHJ[/TD]
[TD]AP001527[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1,2321[/TD]
[TD]$ 16.746,00[/TD]
[TD="align: right"]€13.591,43[/TD]
[TD="align: right"]13.591,43 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]1007019[/TD]
[TD]UIJ[/TD]
[TD]AP001528[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1,2321[/TD]
[TD]$ 6.200,00[/TD]
[TD="align: right"]€5.050,51[/TD]
[TD="align: right"]5.050,51 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]1007020[/TD]
[TD]JIMK[/TD]
[TD]AP001529[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1,2321[/TD]
[TD]$ 35.000,00[/TD]
[TD="align: right"]€28.434,48[/TD]
[TD="align: right"]28.434,48 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]8958952[/TD]
[TD]KYT[/TD]
[TD]AP001530[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1,2321[/TD]
[TD]$2.000.000,00[/TD]
[TD="align: right"]€1.617.992,07[/TD]
[TD="align: right"]1.617.992,07 €[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to have them transfer to 1 sheet and make an overview of the respective machine (drop down list or pivot table)
could you construct me a VBA code for it?

Thank you in advance.

 

[My Aswer Is This]

Some one else here at Mr. Excel will need to help you. I have very little knowledge about Pivot Tables

 

[Aberdham]

Some one else here at Mr. Excel will need to help you. I have very little knowledge about Pivot Tables

[FONT=&quot]Sub[/FONT][FONT=&quot] CreatePivotTable()[/FONT]
[FONT=&quot]'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet[/FONT]
[FONT=&quot]'Source: www.TheSpreadsheetGuru.com[/FONT]

[FONT=&quot]Dim[/FONT][FONT=&quot] sht [/FONT][FONT=&quot]As[/FONT][FONT=&quot] Worksheet[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] pvtCache [/FONT][FONT=&quot]As[/FONT][FONT=&quot] PivotCache[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] pvt [/FONT][FONT=&quot]As[/FONT][FONT=&quot] PivotTable[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] StartPvt [/FONT][FONT=&quot]As[/FONT][FONT=&quot] [/FONT][FONT=&quot]String[/FONT]
[FONT=&quot]Dim[/FONT][FONT=&quot] SrcData [/FONT][FONT=&quot]As[/FONT][FONT=&quot] [/FONT][FONT=&quot]String[/FONT]

[FONT=&quot]'Determine the data range you want to pivot[/FONT]
[FONT=&quot] SrcData = ActiveSheet.Name & "!" & Range("A1:R100").Address(ReferenceStyle:=xlR1C1)[/FONT]

[FONT=&quot]'Create a new worksheet[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Set[/FONT][FONT=&quot] sht = Sheets.Add[/FONT]

[FONT=&quot]'Where do you want Pivot Table to start?[/FONT]
[FONT=&quot] StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)[/FONT]

[FONT=&quot]'Create Pivot Cache from Source Data[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Set[/FONT][FONT=&quot] pvtCache = ActiveWorkbook.PivotCaches.Create( _[/FONT]
[FONT=&quot] SourceType:=xlDatabase, _[/FONT]
[FONT=&quot] SourceData:=SrcData)[/FONT]

[FONT=&quot]'Create Pivot table from Pivot Cache[/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Set[/FONT][FONT=&quot] pvt = pvtCache.CreatePivotTable( _[/FONT]
[FONT=&quot] TableDestination:=StartPvt, _[/FONT]
[FONT=&quot] TableName:="PivotTable1")[/FONT]

[FONT=&quot]End[/FONT][FONT=&quot] [/FONT][FONT=&quot]Sub

I found this, perhaps it would help?

do you perhaps know how to transform the data in all 3 sheets to a new sheet as an overview with VBA code?


[/FONT]