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:

Invoice type	Invoice Number	Supplier/Debitor	Description	Invoice Date	FX rate	USD Amount	EUR Amount	Change in Inventory	Machinery	Category
A	K8554214	ADA	Deposit SN 844451	01/12/2017	1,1885	$361.067,54	303.801,05 €	-303.801,05 €	1
A	K8554215	ADA	final payment_ESN 848462	01/01/2018	1,1993	$358.718,75	299.106,77 €	-299.106,77 €	1
A	K8554216	APOM	final payment_ESN 848462	02/02/2018	1,2492	$ 2.600,60	2.081,82 €	-2.081,82 €	1
A	85426589	APOM	inspection	02/02/2018	1,2492	$ 3.461,33	2.770,84 €	-2.770,84 €	1
A	85426589	UIJ	opmen	02/02/2018	1,2492	$ 18.988,94	15.200,88 €	-15.200,88 €	1

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2 (sales)

Type of invoice	Invoice Number	Supplier/Debitor	Description	Invoice Date	FX rate	USD Amount	EUR Amount	Change in Inventory	Machinery	Category
R	AR00214522	ADA	AR00251452	11/04/2018	1,2384	$ 15.222,00	€12.291,67	-12.291,67 €	1

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

sheet 3( purchase)

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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]