move the first part to last part for the same cell into column across sheets

[KalilMe]

hello
I need macro to move the first part to last part for each cell into column B across sheets

Microsoft Excel (1).xlsx
	A	B	C	D
1	ITEM	BR	SALE	RET
2	1	VEG TOMATO SS	12	-
3	2	VEG TOMATO AA	12	12
4	3	FR BANANA TT	200	10
5	4	FR APPLE LL	100	20
6	5	FR PEAR NN	10	-
7	6	FR BANANA QQ	20	-
FRUI

Microsoft Excel (1).xlsx
	A	B	C	D
1	ITEM	BR	SALE	RET
2	1	VEG TOMATO SS	20	-
3	2	VEG TOMATO AA	22	12
4	3	FR BANANA TT	12	10
5	4	FR APPLE LL	12	2
6	5	FR BANANA QQ	12	-
VEGI

Microsoft Excel (1).xlsx
	A	B	C	D	E	F	G
1	ITEM	BR	TY	OR	SALE	RET	BALANCE
2	1	FR BANANA	T001	TT	252	25	227
3	2	FR APPLE	T002	LL	162	42	120
4	3	FR PEAR	T003	NN	15	3	12
5	4	FR BANANA	T004	QQ	42		42
6	5	FR TOMATO	T005	SS	20		20
7	6	FR TOMATO	T006	AA	42	12	30
8	7	FR ONION	T007	AA1			0
9	8	FR ONION	T008	AA2			0
10	9	FR ONION	T009	AA3			0
SFD

Cell Formulas
Range	Formula
G2:G10	G2	=E2-F2

the result should be

Microsoft Excel (1).xlsx
	A	B	C	D
1	ITEM	BR	SALE	RET
2	1	TOMATO SS VEG	12	-
3	2	TOMATO AA VEG	12	12
4	3	BANANA TT FR	200	10
5	4	APPLE LL FR	100	20
6	5	PEAR NN FR	10	-
7	6	BANANA QQ FR	20	-
FRUI

Microsoft Excel (1).xlsx
	A	B	C	D
1	ITEM	BR	SALE	RET
2	1	TOMATO SS VEG	20	-
3	2	TOMATO AA VEG	22	12
4	3	BANANA TT FR	12	10
5	4	APPLE LL FR	12	2
6	5	BANANA QQ FR	12	-
VEGI

Microsoft Excel (1).xlsx
	A	B	C	D	E	F	G
1	ITEM	BR	TY	OR	SALE	RET	BALANCE
2	1	BANANA FR	T001	TT	252	25	227
3	2	APPLE FR	T002	LL	162	42	120
4	3	PEAR FR	T003	NN	15	3	12
5	4	BANANA FR	T004	QQ	42		42
6	5	TOMATO FR	T005	SS	20		20
7	6	TOMATO FR	T006	AA	42	12	30
8	7	ONION FR	T007	AA1			0
9	8	ONION FR	T008	AA2			0
10	9	ONION FR	T009	AA3			0
SFD

Cell Formulas
Range	Formula
G2:G10	G2	=E2-F2

when move to last part should create one space before it and every time i will create more sheets with the same structure and even change or add new data .

 

[DanteAmor]

and every time i will create more sheets with the same structure and even change or add new data .

You agree if the macro, after making the change in each cell, puts a mark on each record, for example in column "Z", that way if you run again, the macro will only work with the unmarked (new records)?

 

[KalilMe]

that way if you run again, the macro will only work with the unmarked (new records)?

can you more explain,please?
I find difficulty understand this part of your comment

 

[DanteAmor]

The first time (before running the macro)
You have this:
VEG TOMATO SS

After the macro, you get this:
TOMATO SS VEG

After you could add new data, you will have something like this:
TOMATO SS VEG
VEG TOMATO AA

If you run the macro again, the result will be this:
SS VEG TOMATO
TOMATO AA VEG

The macro should only consider new records, the result should be:
TOMATO SS VEG (old)
TOMATO AA VEG (new)

In order for the macro to identify which are the new records, we must (somehow) mark the old records.
Putting the mark in column Z of each record is an option of mine. Do you have any other option?

 

[KalilMe]

I think now understood your view and the code couldn't identfy where is the old record or new without any mark .

Putting the mark in column Z of each record is an option of mine

so I accept your suggestion .

 

[DanteAmor]

I need macro to move the first part to last part for each cell into column B across sheets

Try this for all sheets:

Sub move_first_part_to_last_part()
  Dim sh As Worksheet
  Dim c As Range
  Dim ini As String
  
  For Each sh In Sheets
    For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(3))
      If sh.Range("Z" & c.Row).Value = "" Then
        ini = Split(c.Value, " ")(0)
        c.Value = Mid(c.Value, Len(ini) + 2) & " " & ini
        sh.Range("Z" & c.Row).Value = "x"
      End If
    Next
  Next
End Sub

 

[KalilMe]

impressive work !
may you make the code more dynamically without spcify how many items should move after it?
as I understand your code in this line

        c.Value = Mid(c.Value, Len(ini) + 2) & " " & ini

it moves the first part after two items because all of my data in all sheets just three items and this is my bad!!!!
somtimes could be five or six items then should move the first part after four or five items as in this picture, see the row2,7.

Microsoft Excel (1).xlsx
	A	B	C	D
1	ITEM	BR	SALE	RET
2	1	VEG TOMATO SS TG TCM	12	-
3	2	VEG TOMATO AA	12	12
4	3	FR BANANA TT	200	10
5	4	FR APPLE LL	100	20
6	5	FR PEAR NN	10	-
7	6	FR BANANA QQ	20	-
8	7	FR BANANA QQ MM N/L KK	21	-
FRUI

do me favor if there is way to make more dynamically ,please?

 

[DanteAmor]

it moves the first part after two items

Len(ini) + 2, This doesn't mean 2 elements, it means 2 spaces.

I'll try to explain what these 2 lines of the macro do:

        ini = Split(c.Value, " ")(0)
        c.Value = Mid(c.Value, Len(ini) + 2) & " " & ini

Taking this as an example:
FR BANANA QQ MM N/L KK

c.Value = "FR BANANA QQ MM N/L KK"
Split(c.Value, " ")
Separate each word, so we have:
Item(0) FR
Item(1) BANANA
Item(2) QQ
Item(3) MM
Item(4) N/L
Item(5) KK

ini = Split(c.Value, " ")(0)
Then
ini = "FR"

Len(ini)
Length of the content of the ini variable
len("FR")
length = 2

Len(ini) + 2
2 + 2 = 4 (In this operation we have a 4, it does not mean 2 items. Let's pause right now...)

Remember this:
c.value = "FR BANANA QQ MM N/L KK"
If we separate each letter of the text, it would be something like this:
Position 1: "F"
Position 2: "R"
Position 3: " "
Position 4: "B"
Position 5: "A"
Position 6: "N"
etc...

c.Value = Mid(c.Value, Len(ini) + 2) & " " & ini
Do you remember 4?

c.Value = Mid(c.Value, 4) & " " & ini
That means that we are going to extract a part of the text and it is going to start the extraction at position 4.

Then
c.value = Mid("FR BANANA QQ MM N/L KK", 4)
c.value = "BANANA QQ MM N/L KK" & " " & ini
c.value = "BANANA QQ MM N/L KK" & " " & "FR"
Result:
c.value = "BANANA QQ MM N/L KK FR"
----
You have a problem with your data as you have whitespace before AND after the text:

----
Perhaps the following code tweak will help with any spaces you have at the beginning or end of the text, but you should clean up your data.

Sub move_first_part_to_last_part()
  Dim sh As Worksheet
  Dim c As Range
  Dim ini As String, cad As String
  
  For Each sh In Sheets
    For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(3))
      If sh.Range("Z" & c.Row).Value = "" Then
        cad = WorksheetFunction.Trim(c.Value)
        ini = Split(cad, " ")(0)
        c.Value = Mid(cad, Len(ini) + 2) & " " & ini
        sh.Range("Z" & c.Row).Value = "x"
      End If
    Next
  Next
End Sub

 

[KalilMe]

you should clean up your data.

I did it , but it doesn't work . may you check my data,please?

Microsoft Excel (1).xlsx
	A	B	C	D
1	ITEM	BR	SALE	RET
2	1	VEG TOMATO SS TG TCM	12	-
3	2	VEG TOMATO AA	12	12
4	3	FR BANANA TT	200	10
5	4	FR APPLE LL	100	20
6	5	FR PEAR NN	10	-
7	6	FR BANANA QQ	20	-
8	7	FR BANANA QQ MM N/L KK	21	-
FRUI

 

[DanteAmor]

Your data has spaces at the beginning of the text, you must correct them yourself. It is very simple, just edit the cell, go to the beginning of the text and delete what you have as white spaces.