Sap 1 to Sap 2 card transfer

[makinmomb]

100.000.01	GEMMA 40 X 40 = KARNAK BEIGE P GRANITO	FLOOR GEMMA	KARNAK BEIGE	GEMMA	007A	0	remove
100.000.02	GEMMA 40 X 40 = KARNAK GREY P GRANITO	TILE GEMMA	KARNAK GREY	GEMMA	007A	0	remove
100.000.03	GEMMA 40 X 40 = MYKONOS GREYP GRANITO	FLOOR TILE GEMMA	MYKONOS GREY	GEMMA	007A	0	remove
100.000.04	GEMMA 50 X 50 = ARIZONA BEIGE		ARIZONA BEIGE	GEMMA	005A	0	remove
100.000.05	GEMMA 50 X 50 = VERANDA BRICK		VERANDA BRICK	GEMMA	005A	0	remove
100.000.06	GEMMA 50 X 50 = VERANDA BRICK GEOMETRIC	GEMMA	VERANDA BRICK	GEMMA	005A	0	remove
100.000.07	GEMMA 25 X 35 = BOMBAY GREEN		BOMBAY GREEN	GEMMA	016A	0	remove
100.000.08	GEMMA 30 X 44 = ISCANDARONA BROWN DARK		ISCANDARONA BROW	GEMMA	011A	0	remove
100.000.09	GEMMA 20 X 20 = SOLANGE IVORY		SOLANGE	GEMMA	022A	1000.00
100.000.10	CLOSED CARD			GEMMA	PC	0	remove
100.000.11	CLOSED CARD			GEMMA	PC	0	remove
100.000.12	GEMMA 20X30 = FRUTTI BEIGE		FRUTTI BEIGE	GEMMA	020H	0	remove
100.000.13	GEMMA 20X30 = FRUTTI BRICK		FRUTTI BRICK	GEMMA	020H	0	remove
100.000.14	GEMMA LISTELO 10 X 20 = FRUTTI BEIGE		FRUTTI BEIGE	GEMMA	PC	0	remove
100.000.15	GEMMA DECOR 20X30 = FRUTTI BEIGE	GEMMA	FRUTTI BEIGE	GEMMA	PC	0	remove
100.000.16	GEMMA 30 X 30 = ARIZONA MARRON		ARIZONA MARRON	GEMMA	015A	2000.00

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

100.000.01	GEMMA 40 X 40 = KARNAK BEIGE P GRANITO	FLOOR GEMMA	KARNAK BEIGE	GEMMA	007A	0	remove
100.000.02	GEMMA 40 X 40 = KARNAK GREY P GRANITO	TILE GEMMA	KARNAK GREY	GEMMA	007A	0	remove
100.000.03	GEMMA 40 X 40 = MYKONOS GREYP GRANITO	FLOOR TILE GEMMA	MYKONOS GREY	GEMMA	007A	0	remove
100.000.04	GEMMA 50 X 50 = ARIZONA BEIGE		ARIZONA BEIGE	GEMMA	005A	0	remove
100.000.05	GEMMA 50 X 50 = VERANDA BRICK		VERANDA BRICK	GEMMA	005A	0	remove
100.000.06	GEMMA 50 X 50 = VERANDA BRICK GEOMETRIC	GEMMA	VERANDA BRICK	GEMMA	005A	0	remove
100.000.07	GEMMA 25 X 35 = BOMBAY GREEN		BOMBAY GREEN	GEMMA	016A	0	remove
100.000.08	GEMMA 30 X 44 = ISCANDARONA BROWN DARK		ISCANDARONA BROW	GEMMA	011A	0	remove
100.000.09	GEMMA 20 X 20 = SOLANGE IVORY		SOLANGE	GEMMA	022A	1000.00
100.000.10	CLOSED CARD			GEMMA	PC	0	remove
100.000.11	CLOSED CARD			GEMMA	PC	0	remove
100.000.12	GEMMA 20X30 = FRUTTI BEIGE		FRUTTI BEIGE	GEMMA	020H	0	remove
100.000.13	GEMMA 20X30 = FRUTTI BRICK		FRUTTI BRICK	GEMMA	020H	0	remove
100.000.14	GEMMA LISTELO 10 X 20 = FRUTTI BEIGE		FRUTTI BEIGE	GEMMA	PC	0	remove
100.000.15	GEMMA DECOR 20X30 = FRUTTI BEIGE	GEMMA	FRUTTI BEIGE	GEMMA	PC	0	remove
100.000.16	GEMMA 30 X 30 = ARIZONA MARRON		ARIZONA MARRON	GEMMA	015A	2000.00

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

 

[makinmomb]

Hi rick again , your magic is required again

This week I am moving from one software to another

There is a small modification required , your macro works
fine on one series e.g. 100.000.01 and so

But I have series

102.000.01
103.000.01

Going up to 300

So what I needed added is the macro to do the following

Delete as it does
Then rename the way it does but this time to take care of 102. 103. and so on

 

[Rick Rothstein]

There is a small modification required , your macro works
fine on one series e.g. 100.000.01 and so

But I have series

102.000.01
103.000.01

Going up to 300

Do the 102.... and 103.... etc. series appear on separate sheets or are they listed one under the other on the same sheet? If the latter, is there any blank rows between the individual series?

 

[makinmomb]

Do the 102.... and 103.... etc. series appear on separate sheets or are they listed one under the other on the same sheet? If the latter, is there any blank rows between the individual series?

same sheet

 

[Rick Rothstein]

same sheet

What about my "blank rows" question... are there blank rows between the individual series or do they simply follow one after the other in a long continuous list?

 

[makinmomb]

They follow without row

 

[makinmomb]

I can insert row too if the macro is easier that way ,
I have an insert row macro that looks as follows

Sub InsertRow()
Dim i As Long
For i = Range("A" & Rows.Count).End(xlUp).Offset(-1).Row To 1 Step -1
If Split(Cells(i, 1), ".")(0) <> Split(Cells(i + 1, 1), ".")(0) Then
Cells(i + 1, 1).EntireRow.Insert
End If
Next i
End Sub

 

[makinmomb]

long list that goes up to 15000 rows

 

[Rick Rothstein]

I can insert row too if the macro is easier that way

No, I just needed to know if I had to work with or around them, that is all. Give me a little bit of time and let me see what I can code up for you.

 

[Rick Rothstein]

Okay, give this macro a try...

Sub DeleteRowsWithRemoveInColumnH()
  Dim LastRow As Long, Addr1 As String, Addr2 As String
  Application.ScreenUpdating = False
  Columns("H").SpecialCells(xlConstants).EntireRow.Delete
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Addr1 = "A1:A" & LastRow - 1
  Addr2 = "A2:A" & LastRow
  Range("A1").Value = Application.Replace(Range("A1"), Len(Range("A1")) - 1, 2, "01")
  Range(Addr2) = Evaluate(Replace(Replace("IF(LEFT(@,3)<>LEFT($,3),REPLACE(@,LEN(@)-1,2,""01""),"""")", "$", Addr1), "@", Addr2))
  Range(Addr2).SpecialCells(xlBlanks).FormulaR1C1 = "=LEFT(R[-1]C,8)&TEXT(RIGHT(R[-1]C,2)+1,""00"")"
  Range(Addr2).Value = Range(Addr2).Value
  Application.ScreenUpdating = True
End Sub

 

[makinmomb]

Thank you very much Rick , this is more than Magic

My final question for tomorrow though , since my
pricelist have the old codes , I will need to know
what the prior code was

This should be possible maybe insert a column
and then pull its old code

This for tomorrow sir , as today you have given
me the punch answer already