automatically fill in a worksheet according to columns via VBA

[marreco]

Hi.
I have two one worksheet called "Automatic_filling" and other
"Automatic_filling"
I would like to complete the worksheet "Automatic_filling" so automatically.
As for the filling lines of plamilha "Automatic_filling" spreadsheet "Automatic_filling" will be populated automatically for row by row, column and column.

in yellow are the columns that are part of both sheets.
Thank you!

[marreco]

Hi
Any idea?

Thank you!!

[HaHoBe]

Hi, marreco,

your workbook shwos the sheets "Manual_filling" (with data) and "Automatic_filling" (only headers). So I guess you would like to get the informtaion from Manual_filling to Automatic_filling - what about the information after having been copied? What about if information is modified or added? Copy all data or only data which for example doesnīt show "CANCELADO" in Column E?

Ciao,
Holger

[marreco]

Hi.

your workbook shows the sheets "Manual_filling" (with date) and "Automatic_filling" (only headers). So I guess you would like to get the informtaion from Manual_filling to Automatic_filling - what about the information after having copied Been?

yes!

What about if information is added or modified?

if it is modified or added, then the worksheet "Manual_filling" So be modified in the spreadsheet "Automatic_filling"

Copy all data or only data for Which example does not show "CANCELLED" in Column E?

copy all data in columns painted YELLOW.

Thank you!!!

[HaHoBe]

Hi, marreco,

hereīs the part for copying any altered or added information from Manual to Automatic. Code goes behind Sheet Manual_filling:

Please Login or Register  to view this content.

We may restrict the area on which to work as in your workbook the UsedRange goes way beyond the borders of the table.

The following code copies the data from Manual to Automatic. As data will be updated via the procedure I used a static code for that which goes into a normal module and has to be called manually (at least once to copy the data). I used the range like I suggested it from the workbook:

Please Login or Register  to view this content.

You may apply Appilcation.ScreenUpdating = False to the start of the procedure to avoid flickering.

Ciao,
Holger

[marreco]

Hi.

Was perfect!!
your skills with VBA is amazing!
thank you very much!!

[Naja]

Hello Marreco,

If not an issue can you please put the workbook with the vba codes. I am not sure where to put Hahobe codes and how it is supposed to work.
Thank you

[Naja]

Hello Holger,

We need to put both of your codes? Where should we insert your second code&

Thank you
Danke

[HaHoBe]

Hi, Naja,

please find the workbook with the codes attached. But I like to point out that this is not an absolute safe way to work with. Say you would enter another column all the information for the WorksheetChange-code needs to be updated. And if any row were deleted from the Manual_filling-sheet the information would not fit between the two sheets. There is another way of using the Number from Column A to determine if information needs to be updated (and in which line in Sheet Automatic_filling) or amended. That procedure would utilize Find, build a range and see if the range is set (information in that row needs to be updated) or is nothing (information needs to be amended).

Ciao,
Holger

[Naja]

Vielen Danke Holger!!!

(How can update the code?)

[HaHoBe]

Hi, Naja,

update to what? Could you please explain what you mean by that?

Ciao,
Hoolger

[Naja]

you said in your last post that if we add another column we need to update the code.
Which code? How?

Thank you

[HaHoBe]

hi, Naja,

if you only want to transfer newly edited or updated code itīs the Worksheet_Change-Event, if you want it for the first setting as well itīs both codes.

If you look at marrecoīs example you will see the columns on Sheet Manual_Filling arenīt following up but shall do so in Sheet Automatic_filling. Thatīs whatīs done by

Please Login or Register  to view this content.

The same goes for the row where the data will be transferred to as with Sheet Automatic_filling itīs 4 roows down to Sheet Manual_filling.

Please mind as well that only cells with the yellow filling will be checked for data.

HTH,
Holger

[Naja]

Vielen Danke Holger!! You really are an expert.

One last question. How can we add a condition to your code. For instance, to only make appear in the automatic filling sheet values superior to 50,000 for instance. Any condition.

Thank you

[HaHoBe]

Hi, Naja,

there is a condition in the code (here from the Worksheet_Change-event):

Please Login or Register  to view this content.

This will decide on whether to proceed with the code to check and copy only if the inside color is yellow.

Depending on whether you want to change this criteria you could use

Please Login or Register  to view this content.

or have both criteria like

Please Login or Register  to view this content.

Ciao,
Holger

[Naja]

Again Vielen Danke Holger.

You are very god at this. I am still an amateur.. what do you recommend me to do to ameliorate myself? Is there any book your recommend or so?

[HaHoBe]

Hi, Naja,

maybe you should start with having a look at New to Excel Programming and looking for learning materials . Iīd work my way from the online sources to books (look for free chapters from Wiley for example) or a well sorted book store where you may take your time reading what you are going to pay for.

Most essential tip: do not only read or look at the example codes provided - type in the codes (youīll learn to remember the commands a lot easier this way).

Ciao,
Holger