VBA copy and paste with criteria and different sheets

[sokour]

Hello everyone,


I am a beginner in VBA and I try to learn because I have a lot of reporting that I would like to automate.


I would like to know if it is possible to help me on the construction of a macro.


For a reporting, I import the data that will be on the sheet "source data".
I would now like to take the data and put it according to criteria on the "control" sheet.
So first in "Source Data", I only select the lines or appear "103" in column A:
- I copy all the data from the sheet "Source data" and sheet "total" to the sheet "Control" and start from box C10 and continue until all lines 103 have not been all copy glue .
Column C ("Source data") to / from "C10" ("Control").
Column E ("Source data") to / from "D10" ("Control").
Column M ("Source data") to / from "E10" ("Control").
Column M ("Source data") to / from "E10" ("Control").
Column I ("Total") to / from "G10" ("Control").
Column R ("Source data") to / from "H10" ("Control").
Column T ("Source data") to / from "I10" ("Control").
Q column ("Source data") to / from "J10" ("Control").
"K10" = ((H10 * G10) / 100) / I10
"L10" = ((J10-K10) / J10) - Set Percent.
Column F ("Control") from F10 = according to D10 (code) we will go to column B ("total") and if ya match we take the result in column C (name).
We redo and copied to the last line where there are 103 in the sheet "Donnee Source".


I left 2 lines in the "control" sheet to show the result.

[daboho]

sokour try this

Please Login or Register  to view this content.

[jindon]

sokour,

You need to check your formula in Col.K & L in bold.

Please Login or Register  to view this content.

[sokour]

Thanks for the reply,

But its a little bit complicated for a beginner like me

why this part : Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;HDR=No;"
.Open ThisWorkbook.FullName
End With

Thank you can we simplify ?

[daboho]

Thanks for the reply,

But its a little bit complicated for a beginner like me

why this part : Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;HDR=No;"
.Open ThisWorkbook.FullName
End With


Thank you can we simplify ?

Try my code is so simple

[jindon]

Thank you can we simplify ?

That is to enable Active Data Object to connect to a data and it's a fixed string depends on the version of Excel.

Perhaps the link below will help.

https://analysistabs.com/excel-vba/a...ting-database/

This would be easier to understand.
Again, you will need to review your formula in bold.

Please Login or Register  to view this content.

[sokour]

I try your code but it didnt take in consideration from what I need : "So first in "Source Data", I only select the lines or appear "103" in column A:"

thank you can you help to add this criteria ?

[sokour]

Try my code is so simple

I try your code but it didnt take in consideration from what I need : "So first in "Source Data", I only select the lines or appear "103" in column A:"

thank you can you help to add this criteria ?

[jindon]

I don't understand what you are talking about.

[sokour]

Sorry jindon, I mean for the code of daboho : the code didnt take in account the criteria we select the line with 103 on the column A

Thank you

[jindon]

OK, understood.

[sokour]

OK, understood.

But I would like to thank for the time you help< Iwill try your last code.
But as A begginer I want to learn as well do you think its ok if you can add the commentary on the code ?

Thank you!

[jindon]

The last code is written very simple using AutoFilter.

If you want to understand the code, step debug for yourself first.

While you are in VBE:

Shrink the window to see your worksheet behind VBE.
Click somewhere on the code and hit F8.

As you hit F8, code execute one line and you will see the action on the worksheet at the same time.

And if you still have problem, ask the line(s) that you don't understand.