split Tables into multiple Tables for multiple sheets with different structure & arranging

should split from the header until lastrow before new header for new range .

is there any help?

thanks
I note some items are missed for row2 & row25 for sheet BS Q 4286.pdf
will give BS 1200 R20 18PR THI , the right is BS 1200 R20 18PR G580 THI
BS 265/70R18 JAP , the right is BS 265/70R18 D684 JAP

Hi,

how is the workbook before state : all the 3 data pdf worksheets already exist ?

all the 3 data pdf worksheets already exist ?

absolutely yes .

So no need to « rename the sheet as you wrote » or … ?

So no need to « rename the sheet as you wrote » or … ?

why you suggest this?
my aim for that check data what is in inside for each sheet and compare with the original data for each PDF file .
at least make easy to select specific sheet based on matching with PDF file name

Yes with Excel basics the code should be shorter, less than 70 codelines …

As my aim it to make understand the threads creators that a before workbook & an expected result attachment

ok . but I put the result for three sheets how should split based on tt sheet and support so with details as in OP .

could 'highlight' an explanation in order to have more chance to receive some help quicker …

but I don't understand what you mean ?
which explanation need highlight ?
I'm afraid you mean my thread is not clear totally .
I know this data are chaos , but this is not from me . this happens after export PDF file as in excel, that's why I posted thread to arrange this data by vba

According to your last post it seems the original subject has changed ! No compare mention in your original 'explanation' …

no no ! I don't changed original subject . just I gave you my reason why rename sheets as in column A ,Naturally will be the same PDF files names .
my requirements stay as what I posted in initial post .

thanks

Following up on your replies: do you always receive your data as PDF files? If so

yes

it would (must!) be worth considering writing an an "Excel-to-PDF" conversion/cleanup macro producing a single file format AND common field (column) headings.

yes I agree with you , and I already know that . in this time I have to split and arranging for each separated sheet firstly and I will create another sheet with single field (column) headings and merging whole sheets by another macro has already existed .
I have some notices:
1- the macro doesn't create sheets automatically . I have to create manually , this is not problem
2- and will change data from TABLE to range . also this is not problem I will change to table after run the macro
3 in this point I want fixing as I said

should update the sheets have already divided when update TT sheet

it means any change in tt sheet should automatically change in others sheet are divided.

Is the tt sheet changes everyday ? So another way is to create an event for each of the 3 already created worksheets
- but hoping there are only 3 sheets and that will never change ? -
like for example each time when activating a sheet, even without any new data
but according to your small data attached workbook the result should be instant …

But o b v i o u s l y rather than such gas factory VBA procedure as the import is not correct - no sense to mix data like that ! -
the appropriate way is to import only one file directly to its relative worksheet !

You wrote « the macro doesn't create sheets automatically » but you yet answered « absolutely yes » for the before state
so meaning no need to create it ‼

You wrote « will change data from TABLE to range » but o b v i o u s l y your attachment expected result contains
a sheet with no table, your bad !
You must be a lot more accurate when creating such thread and its attachment.
So yes when nobody answers that means your initial post is not clear enough …

Originally Posted by JohnTopley

I am sure Marc L will have some "smarter" code!

Maybe, maybe not, but as from post to post his explanations are contradictory
and as just importing correctly the data so such after bad import VBA procedure is totally useless so why wasting time ? …

According for what I thought yesterday to have decypher his need - but very not sure today after his new posts ! -
just using Excel basics in a VBA procedure just replicating what an Excel beginner can operate manually
I was 'seeing' a shorter code with around 60 codelines …

According to your attachment 2 destinations sheets use a table but the third does not contain any table ‼

this is not big problem(minor). as I mentioned TABLE whether in title of thread or in OP become this error useless if you like mentioned that to warn me will I take it as consideration or correct but this is not obstacle to others members try to help me . I see in this forum there is more big mistake than this and the members inform owner's thread to this mistake.

So you forgot to indicate there are more than 3 sheets ‼

forgot !!!
why I have to mentioned that if I have the same structure as in different structure for three ranges !

So according to your initial 'explanation' we can only hardcode these 3 sheets but can do nothing for the others
without at least a crystal clear elaboration.

multiple ranges as in attachment without any else different I don't mentioned doesn't make difference

with the help you already received from John you can fit his code for the other destination sheets when necessary …

without any doubt , but the code will be huge by adding more lines when add new sheets .
thanks for your time.

The code can be not such huge if you find at least a strategy (logic) to detect if a destination sheet matches one of the three structures
if only there are only three (if it's the case so why such poor initial attachment ?!) …
But for more data structures yes you will have to add more codelines.

I agree with previous John's post as it can't be professional (lack of brain),
just good enough for some student training using the Macro Recorder & Excel basics …

I forgot an important point :

Originally Posted by abdo meghari

it means any change in tt sheet should automatically change in others sheet are divided.

No !

Only with smart workbook with smart worksheets with smart data with an unique smart layout
with smart project conceptor with smart Excel users …

When nothing is smart like here so in case of any change you will have to click on a button
in order to launch the VBA procedure deleting first all sheets after 'tt' sheet
(according to bad inconsistent attachment design)
then split the 'tt' data according to its column A in new sheets created after 'tt' sheet …

If you do not want to operate the button each time so confirm it in order I definitively quit this thread !

if only there are only three (if it's the case so why such poor initial attachment ?!) …

I no know why you said poor!
do you mean size of rows are little ?

Only with smart workbook with smart worksheets with smart data with an unique smart layout
with smart project conceptor with smart Excel users

so you want to say with this data can't success at all ?!

you will have to click on a button

this is not problem, why not?

If you do not want to operate the button each time so confirm it in order I definitively quit this thread !

ok I accept clicking button each time .

I agree with previous John's post as it can't be professional (lack of brain),

so what's missed to want to know ?
I said that bad data like this way happens while import data from file PDF
I gave tt sheet should split based on column A contains files names , each file name contains different data should be arranging as I did it with some details how arrange and add missed items and delete some columns .
if you say this data are not acceptable , then I will consider your answering can't arrange this data.
I still don't know what exactly you want to post it

we need to know ALL possible formats of the PDF files you receive and for you to agree the final output format with (for example) consistent headings.

first thank you for your code .
based on attachment these are only format , not more , just I will add another ranges with the same format as I did it in attachment .
there is no different format with comparison the attachment . so all of data what ever how many sheets will be as in three ranges in attachments when add many ranges .

Most of your previous posts have highlighted inconsistent formats which is (surely) not a very effective nor efficient way to process your data.

yes that's correct and you have ever said me , but in this case the circumstances is entirely different.

Originally Posted by abdo meghari

it means any change in tt sheet should automatically change in others sheet are divided.

Originally Posted by Marc L

No !

My bad !
In fact possible just reproducing the same way than splitting all names but just for the one where the change occurs.
Blind I was 'cause of all the bad things within the initial attachment …

So just creating a stand alone VBA procedure for each data structure / expected result layout
in order to use it whatever when splitting all names or when a change is made in 'tt' worksheet, the modular way !
So the code - two times heavier than what I thought according to the initial post - should contain three data layout procedures,
a procedure splitting all names and the Change worksheet event procedure …

But as often under Excel different ways are possible to reach the goal, even just using Excel basics.
According to the initial attachment, any should be good enough.
But with a workbook with much more data, I can't guess which way to go, which should be the slower or the faster …

How many rows / source names in your real workbook ?

On our side the circumstances are the same than before …

before yes from me, but now no !

Anyway, as in case I post an Excel basics VBA procedure according to your initial attachment it will be an one shot starter,
I will not modify it for what your forgot / misexplained, to be sure to not waste any time,

yes based on attachment , I don't need add more requirements to waste your time

in 'tt' sheet column A you can have for example 20 files but only with three possible data structures
and only three expected layouts results like in your initial attachment ?

yes but it will increase sheets addition based on files are existed in column A .

According to post #23 did you think at least about a strategy ?

the only strategy I see should arrange the files when they are EXCEL files before save files as PDF but this is not available because it will received by another company when purchase from of it , I can't enforce company to do that . so alternative strategy should arrange every file for each sheet alone.

To John : did you start something ? So in this case I let you ending your code …

I'm not sure what you mean for that .

In fact possible just reproducing the same way than splitting all names

you mean in the same tt sheet?
I think if you did it maybe the code will crash because of the new ranges addition comparison with ranges have already arranged when clicked macro earlier .

So just creating a stand alone VBA procedure for each data structure / expected result layout
in order to use it whatever when splitting all names or when a change is made in 'tt' worksheet, the modular way !
So the code - two times heavier than what I thought according to the initial post - should contain three data layout procedures,
a procedure splitting all names and the Change worksheet event procedure …

I hope you understanding the rest of details about add the last items based on first sheet with tt sheet and concatenate items from two columns to column and ignore some columns as John did it in post#15.

But with a workbook with much more data, I can't guess which way to go, which should be the slower or the faster …

this is not big and it will differentiate from month to another when send me the files.

How many rows / source names in your real workbook ?

The maximum will be 2000 rows

Originally Posted by abdo meghari

the only strategy I see should arrange the files when they are EXCEL files

So you misread post #23 !
According to 'tt' worksheet, what is the strategy to detect the data structure / the expected result worksheet layout ?

I already have one based only on your too small initial post attachment but no idea if could well work on the real data.
As I don't know which way to go, even using only Excel basics the code may have 150 codelines,
a way using more Excel formulas than the other, so in case you have to fit the code yourself
you may need all the summer and maybe the winter to well understand it ‼

And as my evaluating tool says a not go or waste my time, often it's right but maybe it's wrong 'cause of few data,
I already see a trap but I suspect another one - guessing time but as guessing can't be coding ! - so
maybe the reason of his negative return …

Originally Posted by abdo meghari

you mean in the same tt sheet?

I mean after the 'tt' data split and arrange VBA procedure if you manually modify a cell within this 'tt' worksheet
the relative split worksheet can be automatically updated - I was wrong yesterday - without launching
again the split and arrange procedure …

Originally Posted by abdo meghari

I hope you understanding the rest of details

As we are hoping you have enough well explained …

Originally Posted by abdo meghari

The maximum will be 2000 rows

So why not attach such workbook in particular if you are not able to elaborate the strategy ?
Save the workbook as binary format .xlsb in order to reduce its size, just in the state after such bad data importation …
Then I could better see the traps.
As a reminder, the more data attachment, the more chance you won't have to fit the 150 codelines - maybe more ! - yourself …

According to 'tt' worksheet, what is the strategy to detect the data structure / the expected result worksheet layout ?

I'm not sure what you mean?
if you want standard , then you can depend on repeated item(file name) from the first row until to last row for the same item and before header row foe new item(new file name).

As I don't know which way to go, even using only Excel basics the code may have 150 codelines,

it's like john's code.

if you write code based on three ranges as in initial post naturally should not be problem when add new ranges as the structure in file
so I don't need

so in case you have to fit the code yourself

you may need all the summer and maybe the winter to well understand it ‼

bad expression!
it's not necessary to comment like this way

I already see a trap but I suspect another one - guessing time but as guessing can't be coding ! - so

what's trap you're talking about ?

So why not attach such workbook in particular if you are not able to elaborate the strategy ?

first sensitive data
second to make easy the members see a little data instead of follow the whole data , could be bored.

Save the workbook as binary format .xlsb in order to reduce its size, just in the state after such bad data importation …
Then I could better see the traps.

I bet there is no traps , if insist for attaching file with more data I will see what I can do tomorrow.

As you did not answer for the Excel table so well read again my previous post in order I get at least the expected answer !
If you modify the original 'tt' data with creating the Excel table so do not, no sense wich such mixed data.
As your last attachment is just good enough to be trashed so hoping the next one will better reflect at least your real workbook
without this time such data errors …

if you mean the code doesn't deal any changes for tt sheet after run your code . ok ignore it , just I will import data from the files , then your code will arrange them and no need changes for tt sheet after the macro arrange all of data

No you misread again as I do not mean anything but what I exactly wrote !

Eight Ball Deluxe, same player shoot again ! Last try :

Originally Posted by Marc L

first do you really receive the 'tt' data like an Excel table or is it you who created this Excel table ?

Originally Posted by Marc L

If you modify the original 'tt' data with creating the Excel table so do not

So how really is the raw 'tt' data worksheet when you receive it ?

first do you really receive the 'tt' data like an Excel table or is it you who created this Excel table ?

ok creates table automatically when convert PDF to EXCEL without interfering from me .

So how really is the raw 'tt' data worksheet when you receive it ?

there is no raw tt' data worksheet when you receive it . in reality this sheet created automatically when covert PDF to excel and create Table automatically , but I do only renaming sheet manually .

I would use another workaround according to Dumb or Dumber who creates the table with the first data row as the header table,
another no sense, again a guy which does not know how Excel rules ! So a red light becomes orange but not green …
Trying to code something without the need to change anything in the workbook, like the header or like adding Poland in 'liss' worksheet …

Many other red lights, so questions to follow but I wait first for your revert according to John's code.

Like Marc, I found a number of "bugs": long sheet name But I think this is a typo), duplicate PDF nanes, "N/A" entries.

sorry john about it . in reality this is really existed in PDF file this is not typo, but don't care of that I will delete it manually.
seem your code works very well, but why you use range instead of dynamic lastrow ?
then I have to rename range for ttt sheet every time when I add new ranges for new files in ttt sheet
and the tt sheet is not important so I can delete it , is that right?

another no sense, again a guy which does not know how Excel rules ! So a red light becomes orange but not green …
Trying to code something without the need to change anything in the workbook, like the header or like adding Poland in 'liss' worksheet …

I'm not sure what you try to say ,sorry !

Originally Posted by JohnTopley

So perhaps Marc can point me in the right direction with regard to TABLE vs RANGE: or equally he may already have such code already.

Originally Posted by Marc L

Trying to code something without the need to change anything in the workbook, like the header or like adding Poland in 'liss' worksheet …

Yes it can be achieved according to the attachment as it is
so without modifying the 'tt' worksheet neither creating such 'ttt' helper worksheet
neither adding POLAND for example to 'liss' worksheet according to the initial post attachment …
As a table can be read like a range - in fact a table is just a specific range - so it's very not a question of table versus range.

To go further I just need all lights green but as abdo seems to have difficulties to just well read and answer to questions …

abdo : do you stay with John's code or should I ask red lights questions ?

Originally Posted by abdo meghari

but why you use range instead of dynamic lastrow ?

According to your both static attachments just using Excel basics 'dynamic lastrow' is useless !
As 'dynamic' has not much sense, last row is enough …

To go further I just need all lights green but as abdo seems to have difficulties to just well read and answer to questions …

my English language is bad that's why I find difficulties to understand your expression sorry!

abdo : do you stay with John's code or should I ask red lights questions ?

it's up to you, which red lights question?
if you still interest tell me all of red lights.

According to your both static attachments just using Excel basics 'dynamic lastrow' is useless !

did you see john's files contain specific range . so when add new ranges it need create range again by delete the previous so how is useless?

My attempt defining "ttt" as table.

I will see tomorrow . thanks for your efforts.

Question #1 of 4 :

according to your initial post where you wrote

Originally Posted by abdo meghari

column B for PR-2023-00001sheet=column C for TT sheet=item code

but with your initial post attachment following your direction
just using Excel basics manually I can't get exactly your expected result in column E :

Q1.jpg

So is it a column error reference like Item Code instead of Description or
did you make your bad attachment non matching your explanation ?

So is it a column error reference like Item Code instead of Description or
did you make your bad attachment non matching your explanation ?

OMG!!
you're correct . should DESCRIPTION column based on result in file
red lights doesn't seem to stop for lighting.

@JohnTopley
it will shows application defined error if I add new range with same structure as in existed from the beginning .

Next question : according to your post #36 last attachment for the PR-2023-00003 data is it normal the empty 'Sr' column ?

this is because I copy data manually . in realty will filled numbers 1,2,3 as in BS Q 4286.pdf file in last column ,

if you want to manually correct the 'tt' worksheet or if you prefer a 'Sr' automatic numbering.

Neither this nor that . will auto fill when import form original PDF file contains numbers and the word is ITEM not SR as in BS Q 4286.pdf file

In the last case, as 'Sr' is like an ID for each data row so the 'tt' worksheet Change event VBA procedure
can not update anymore just the result worksheet relative cell to be changed but must reload all this worksheet data.

this is not problem to update data , then I will delete whole data in tt sheet and re- import again from PDF files to do that .

So Ok, could be a green light until I read your comment about 'Sr' header
as according to your initial post attachment two of three results worksheets use 'Sr' as header so why 'ITEM' for the third ?

Ok John. But if I share some code I hope you could take it a glance …

as according to your initial post attachment two of three results worksheets use 'Sr' as header so why 'ITEM' for the third ?

ok . I thought the header is ITEM in tt sheet but is COLUMN7 , so you can make it one single as SR .

I add a new PDF to the last file I posted and had no problem

do you mean when add new file in ttt sheet then will add new sheet for this file? if it's so I will check again and attach your attaching .

First your explanation is contradictory according to your own rules :
A) « if the last item contains short letters like JAP or THI then don't add any thing »
versus
B) « if the last item … IND and the adjacent cell is Indonesia … then the last item … will become INDO » …

Following rule A so rule B can't be applied ‼ So the elaboration should better be :

yes another my really bad !

if the item contains IND in last item then will replace with of INDO but if there is no short letters and adjacent cell is INDIA then will add to last item is INDI
and if the last item is INDI then should not add it .

But in your post #36 attachment a 'new country' appears : N/A ! I have no clue, maybe New Argentina, whatever …
So following the rules, in particular the POLAND sample in your initial attachment when the country does not exist in the 'liss' worksheet,
this is the result for PR-2023-00004 :

no this is new country and ignore that totally. I checked the original PDF file and when import data will be in excel like this
450 N/A BRIDGESTONE BS 650R16 R230 JAP 31
so the the last item should not add any thing because JAP is existed

So is it the expected result ?
If not, what must it be, what is the mandatory missing rule ?
And you should better explain all the specific cases 'cause as a reminder
I will post a one shot code so I won't modify it for what you forgot / misexplained …

Such poor initial post attachment not well reflecting your real data just leads to a waste of time,
maybe the reason why - with the foggy explanation - you did not receive any help during several days …

As on my side it was the case, your initial post looks like you did not ever well understand your need !
As always with your threads the difficulty is very not to write a code but to decypher your need, very time consuming,
certainly the main reason why the helpers here on their free time, often short, prefer to directly move to the next threads
in order to help several people well following the forum rules (whatever the Excel forum, it's the same)
rather than consuming all their free time for your thread and finally won't help …

my apologies !

I really regret publishing such a bad topic, and the word bad does not express the truth of the matter, but rather it is a big mess. My mind is in another place I thought if we didn't understand each other well but now every word you were saying is true even though sometimes you use sarcastic expressions on the topic sometimes I should have accepted that as a result of publishing this topic in a disgusting way.
I think after many publications and with John's wonderful help, I should review myself when raising a topic to include all the accurate and correct details instead of publishing a topic that does not benefit the forum and members.

If I well understood N/A can't exist in the real data ? If so then it could be a direct new green light but as you wrote

Originally Posted by abdo meghari

when import data will be in excel like this
450 N/A BRIDGESTONE BS 650R16 R230 JAP 31
so the the last item should not add any thing because JAP is existed

New issue here as JAP is not the last item ‼
So according to the rules JAP is added after 31 so the final result is 450 N/A BRIDGESTONE BS 650R16 R230 JAP 31 JAP …

New issue here as JAP is not the last item ‼
So according to the rules JAP is added after 31 so the final result is 450 N/A BRIDGESTONE BS 650R16 R230 JAP 31 JAP …

just I copy the whole row how could be when import from the file .
each item is in existed in column . 31 is in existed in column SR
QTY ORIGIN MARK BRAND SR
450 N/A BRIDGESTONE BS 650R16 R230 JAP 31

What a confusion ! So N/A exists ‼
What is the rule to follow in such case ? Do nothing, not compare ORIGIN & BRAND so BRAND stays as it is ?

What is the rule to follow in such case ? Do nothing, not compare ORIGIN & BRAND so BRAND stays as it is ?

the rule when the adjacent cell for last item contains JAP for N/A cell then keep the last item JAP
if you see that is not sense , then I will clear N/A manually and write JAPAN and finish this problem.
so what you think?

OK

POLAND=POL
N/A=JAP
I will add to Liss sheet

Ok so we follow John's way : when the country / ORIGIN does not exist in 'liss' so do nothing,
check only the 'liss' existing countries for SHORT text as description last item …
A new green light !

Ok so we follow John's way : when the country / ORIGIN does not exist in 'liss' so do nothing,

yes

check only the 'liss' existing countries for SHORT text as description last item …

I edited file .

Is it again just a bad copy you have done ?

yes that's correct .

but different options according to the Sr duplicates numbers …

I'm not sure what you try to say .

Do you really need result worksheets as Excel tables ?

I want Tables if it's possible . but I don't make hard the thing if you prefer change to range it's ok( tt sheet is always contains TABLES I can't control of it)

must the result worksheets be in the same order as within 'tt' or should be sorted ?

if it's possible by sorting to arrange data .

Ok for tables. I found another error within 'tt' worksheet but I bet it's again a bad copy you have done …

For the sort, I was stating for the result worksheets names order only, not the data … So

1) For the worksheets names order, the same than 'tt' aka BS Q 4286, PR-2023-00001, STOCK B.S TO 31-10-2022, PR-2023-00002 and so on
or sorted like BS Q 4286, PO-2023-000022, PO-2023-000023, PO-2023-000024, PR-2023-00001 and so on ?

2) Each result worksheet data is already sorted according to the 'Sr' column, do you need another sort, on which column ?

Ok for tables. I found another error within 'tt' worksheet but I bet it's again a bad copy you have done …

definitely yes

1) For the worksheets names order, the same than 'tt' aka BS Q 4286, PR-2023-00001, STOCK B.S TO 31-10-2022, PR-2023-00002 and so on

based on tt sheet arranging

2) Each result worksheet data is already sorted according to the 'Sr' column

it's ok . keep as in SR column

When the main VBA procedure ends a 'Rejected' message box may arise just indicating the count number
then you can check in 'liss' worksheet columns E:F like in this fake sample :

Reject.jpg

• The question mark in cell E1 alerts the data structure does not match any of the three expected …
  
  
• The number in E2 is the source name characters length which is superior to the max 31 a worksheet name can handle.

Your post #36 attachment contains a source name with 32 characters length so should be rejected
but just removing the '.pdf' filename extension so it is not rejected anymore,
the reason why I have chosen for the result worksheets names to remove the extension …

ok this is your code and choose what you think is better
Each method may have negatives and positives. It is better to choose the least negatives

I updated my previous post, read it if that can help you more to make your choice.

If I may choose I wanna first know how is created / updated 'tt' worksheet …

option2 could be suitable, but I have question : could delete divided sheets before run the code ?
the old divided sheets will be not matched with tt sheet when import new files . so should delete them and become useless.

Both options #1 & 2 do not need to delete all the result (split divided) worksheets as it's just when a cell changed within 'tt'.
Only the main VBA procedure needs to, like I wrote in post #81 :
« the main procedure splitting the data to new worksheets as it deletes first any worksheet after 'tt' worksheet » …

Let's start with a sample from a workbook containing only 'liss' & 'tt' worksheets : once the code implemented,
you can create a button in order to launch the main VBA procedure splitting & arranging the 'tt' data to new individual worksheets.
Then two cases :

• Without any Change worksheet event :
  no issue when importing new data to 'tt' worksheet whatever via a VBA procedure or manually like via a Copy / Paste.
  But no automatic update in the relative result worksheet when you manually change a data within 'tt' worksheet
  so the only and unique way to update is to launch each time necessary the main VBA procedure …
  
  
• With any Change worksheet event implemented to the 'tt' worksheet module :
  so if a cell is changed within 'tt' worksheet so its relative individual worksheet should be updated
  under conditions like explained in post #81 …
  But it can be a slow mess, may lead to an Excel crash or even a system crash, when another VBA procedure
  like when importing all data to 'tt' worksheet does not desactivate the events just during its process
  or when an entire data Paste is applied to 'tt' …

As you do not answer how 'tt' data is allocated …

If you have well read post #81, Option #2 - like Option #1 - updates only the unique individual worksheet relative to which 'tt' cell changed …
The difference between both is Option #1 trying to be smarter with updating only the necessary when possible
rather than Option #2 updating all the relative unique individual worksheet even when only a 'tt' cell changes …
Well notice above like in post #81 I did not write worksheets but worksheet without an 's' so a single unique one.

According to your post #36 new attachment :

1. Crucial difference with your both previous attachments in cell A1 which contains now the first filename
   versus the previous containing 'Source.Name' as header : so which is the 'good one' when you receive the data ?!
   
   
2. The source 'tt' data as individual worksheet column 'Sr' from PO-2023-000022 has duplicates and
   PO-2023-000024 has a missing number or is not well numbered :
   new strategy to deploy or just again a bad copy you have made ?

versus the previous containing 'Source.Name' as header : so which is the 'good one' when you receive the data ?!

I think you have informed me for this error , if you are comfortable with previous file before updating so ignore modification . actually when import data will add Source.Name in A1 so what you decide about that ?

The source 'tt' data as individual worksheet column 'Sr' from PO-2023-000022 has duplicates

I don't see any duplicates numbers for SR column , and I don't think to adjust anything for PO-2023-000022 file

PO-2023-000024 has a missing number or is not well numbered :

you mean in column A , right?
sorry yes that's correct .

new strategy to deploy or just again a bad copy you have made ?

there is no new strategy .
EDITED File

To be sure when you import 'tt' data : the original cell A1 is a filename or 'Source.Name' ?

As this is the post #81 main question to avoid a crash with the Change worksheet event so how do you import the 'tt' data ?

To be sure when you import 'tt' data : the original cell A1 is a filename or 'Source.Name' ?

OK keep 'Source.Name' as what happens when import data .

so how do you import the 'tt' data ?

as I said from PDF files will add files names in column A except A1 will be Source.Name.

the only way to do that by office 365 is existed at work to convert PDF to excel.

So if it is an Excel 365 feature you manually use
so such Change worksheet event may interfere and could very slow down the process or even may lead to a crash …

So rather than writing 50 codelines for such event which may be a mess this is another alternative close to Option #2 but semi-automatic :
once you finish to change data for an unique filename within 'tt' worksheet then its relative individual worksheet is updated
when you double click or right click - to choose - on the filename within column A ?

If you process changes for different filenames you have the choice to just activate the event for each filename
or to just launch again the main VBA procedure like when you import new data …


According to your new post #36 attachment I will use the 'tt' worksheet used range
as I can't trust anymore the table location including only rows #1 to 100 but the last data is in row #260 !

A doubt according to your initial explanation :

Originally Posted by abdo meghari

also will add new ranges for tt sheet with the same structure as in attachment .

What that really means ?
You add new ranges for new filenames non existing when you import data from pdf files
or you just modify data for only existing filenames with their individual worksheets already created ?

For the first option so any worksheet event procedure is useless as for new filenames the main procedure must be launched.
In this case that will very simplify & reduce the code, just two procedures, one is a sub level called by the main,
its execution may be slightly faster …

as I can't trust anymore the table location including only rows #1 to 100 but the last data is in row #260 !

what do you mean?

What that really means ?

add new files are not existed totally and modifying data for files names are existed .

No click event procedure as explained in post #98 for a lighter code just with the main procedure.

ok this option could be enough.

According to your attachments an one shot Excel basics VBA code to paste only to the Worksheet____4 (tt) module :

Please Login or Register  to view this content.

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

currently testing ......
will come back later

great!
it works without any problem
thanks for this work