VBA code to post data from one sheet to other but in different columns

[RAJESH1960]

Hello guys, This is a little complicated for me to write the code.
Raw Sheet is a formatted sheet received to be converted into XML. The expected result is the workings sheet. Workings is the sheet where there are columns more than the Raw sheet as it contains all the columns needed. Workings is the sheet where I want the data to be posted column wise from the Raw Sheet, with the help of a code. The headings of taxes have one thing in common "input" and the rates are all different. The CGST, SGST and IGST may be anywhere in the value of Input - beginning, middle or end. The code has to search for the rates of sheet Raw and Post them in sheet Workings.
Voucher No. must be posted as Supplier Invoice No. in the workings sheet. The others columns in Working are for the columns of Raw sheet with amounts in figures which are not in Workings sheet. In this case, they are courier charges @5% and Cartage. In other cases it can be different names. Check for columns with amount for others.

Query 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Rajesh
2	Kathriguppe
3	Bangalore - 560042
4	Ph: 000000000
5	Purchase R Register
6	1-Dec-2021 to 31-Dec-2021
7	Date	Particulars	Address	Voucher No.	Supplier Invoice No.	Supplier Invoice Date	GSTIN/UIN	Narration	Gross Total	Purchase GST Local	Input SGST @ 2.5%	Input CGST @ 2.5%	Round Off	Purchase IGST Interstate	Input IGST @ 5%	Input IGST @ 18%	Input CGST @ 9%	Input SGST @ 9%	Input IGST @ 12%	Input SGST @ 6%	Input CGST @ 6%	Courier Charges @ 5%	Cartage	Input CGST @ 14%	Input SGST @ 14%
8	01-Dec-2021	january		440	338	01-12-2021		abcde	20.00	12345.00	93.33	93.33	-0.02				5.00	5.00		10.00	10.00	20.00		176.08	176.08
9	01-Dec-2021	february		441	3192	01-12-2021		defg	32.00					40.00	112.00				1234.00			10.00
10	01-Dec-2021	march		442	47657	01-12-2021			72.00				-0.48	36.00		246.48							30.00
11	01-Dec-2021	april		443	3339	01-12-2021			53.00					80.00	544.00
12	02-Dec-2021	may		444	1839	02-12-2021		hij	68.00				-0.28	665.00	3.78							40.00
13	02-Dec-2021	june		445	10532	02-12-2021			45.00				-0.40	478.00	20.40
14	02-Dec-2021	july		446	1562	02-12-2021			868.00				-0.10	922.00	46.10								50.00
15	03-Dec-2021	august		447	16502	03-12-2021		klmn	714.00	6789.00	170.10	170.10	-0.20				5.00	5.00
16	03-Dec-2021	september		448	1913	03-12-2021			1685.00	1010.11			0.29				1226.80	1226.80				60.00
17	03-Dec-2021	october		449	3852	03-12-2021			2632.00				-0.31	215.00	125.00
18	03-Dec-2021	november		450	3853	03-12-2021			1790.00					160.00					1920.00				70.00
19	03-Dec-2021	december		451	577	03-12-2021		rstu	748.00				0.15	712.00	35.00
20		Grand Total							5764634.00	3093454.56	76226.83	76226.83	5.49	2379855.78	113446.25	3516.48	2663.34	2663.34	11363.54	780.20	780.20	3300.00	-1.00	176.08	176.08
Raw

Query 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	a	b	c	d	e	f	g	h	i	j	Date	Supplier Invoice Date	Narration	Supplier Invoice No.	Voucher No.	Particulars	Gross Total	k	l	m	n	o	p	q	r	CGST INPUT @ 2.5 %	SGST INPUT @ 2.5 %	CGST INPUT @ 6 %	SGST INPUT @ 6 %	CGST INPUT @ 9 %	SGST INPUT @ 9 %	CGST INPUT @ 14 %	SGST INPUT @ 14 %	IGST INPUT @ 5 %	IGST INPUT @ 12 %	IGST INPUT @ 18 %	IGST INPUT @ 28 %	Round Off	Purchase GST Local	Purchase IGST Interstate	Others 1	Others 2	Others 3	Others 4
2											#########	01-12-2021	abcde	338	338	january	20.00									93.33	93.33	10.00	10.00	5.00	5.00	176.08	176.08					-0.02	12345.00		20.00
3											#########	01-12-2021	defg	3192	3192	february	32.00																	112.00	1234.00					40.00	10.00
4											#########	01-12-2021		47657	47657	march	72.00																			246.48		-0.48		36.00		30.00
5											#########	01-12-2021		3339	3339	april	53.00																	544.00						80.00
6											#########	02-12-2021	hij	1839	1839	may	68.00																	3.78				-0.28		665.00	40.00
7											#########	02-12-2021		10532	10532	june	45.00																	20.40				-0.40		478.00
8											#########	02-12-2021		1562	1562	july	868.00																	46.10				-0.10		922.00		50.00
9											#########	03-12-2021	klmn	16502	16502	august	714.00									170.10	170.10			5.00	5.00							-0.20	6789.00
10											#########	03-12-2021		1913	1913	september	1685.00													1226.80	1226.80							0.29	1010.11		60.00
11											#########	03-12-2021		3852	3852	october	2632.00																	125.00				-0.31		215.00
12											#########	03-12-2021		3853	3853	november	1790.00																		1920.00					160.00		70.00
13											#########	03-12-2021	rstu	577	577	december	748.00																	35.00				0.15		712.00
Workings

 

[RAJESH1960]

The most complicated part is the headings of some columns are different and only the percentage of rate matches. And the columns others1,2,3,4 should search for columns with amount and they shouldn't match with the other columns in the working sheet,

 

[johnnyL]

How about:

Sub MoveColumnsToAnotherSheet()
'
    Application.ScreenUpdating = False                                                              ' Turn ScreenUpdating off
'
Dim StartTime                           As Single
StartTime = Timer                                                                                   ' Start the stop watch
'
    Dim DestinationArray1ColumnNumber   As Long, DestinationArray2ColumnNumber  As Long
    Dim SourceHeaderRow                 As Long, SourceLastRow                  As Long, SourceRows     As Long
    Dim DestinationLastRow              As Long
    Dim SourceLastColumnNumber          As Long
    Dim cell                            As Range
    Dim DestinationLastColumnLetter     As String
    Dim SourceLastColumnLetter          As String
    Dim DestinationArray1               As Variant, DestinationArray2           As Variant, SourceArray As Variant
    Dim DestinationWS                   As Worksheet, SourceWS                  As Worksheet
'
    Set DestinationWS = Sheets("Workings")                                                          ' <--- Set this to the destination sheet
         Set SourceWS = Sheets("Raw")                                                               ' <--- Set this to the source sheet
'
    SourceLastRow = SourceWS.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row - 1          ' Get the last source row of data minus the total row
    SourceLastColumnLetter = Split(Cells(1, (SourceWS.Cells.Find("*", , xlFormulas, , _
            xlByColumns, xlPrevious).Column)).Address, "$")(1)                                      ' Get last column letter used in the source sheet
    SourceLastColumnNumber = SourceWS.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column   ' Get last column number used in the source sheet
'
    With SourceWS.Range("A1:" & SourceLastColumnLetter & SourceLastRow)                             ' Look through the source sheet for the header row
        Set cell = .Find("Date", LookIn:=xlValues)                                                  '   Find the header called 'Date'
'
        If Not cell Is Nothing Then                                                                 '   If 'Date' is found then ...
            SourceHeaderRow = cell.Row                                                              '       Save the row # into SourceHeaderRow
        End If
    End With
'
    SourceRows = SourceLastRow - SourceHeaderRow + 1                                                ' Get # of SourceRows including the HeaderRow
    SourceArray = SourceWS.Range("A" & SourceHeaderRow & ":" & SourceLastColumnLetter & SourceLastRow) ' Load Source page starting with Header Row down to last row
'
    DestinationLastRow = DestinationWS.Range("K" & Rows.Count).End(xlUp).Row                        ' Get last row of data of destination page
    DestinationLastColumnLetter = Split(Cells(1, (DestinationWS.Cells.Find("*", , xlFormulas, , _
            xlByColumns, xlPrevious).Column)).Address, "$")(1)                                      ' Get last column letter used in the destination sheet
'
'--------------------------------------------------------------------------------------------------
'
    ReDim DestinationArray1(1 To SourceRows, 1 To 7)                                                ' Set DestinationArray1 row and column size
'
    For DestinationArray1ColumnNumber = 1 To UBound(DestinationArray1, 2)                           ' Loop through DestinationArray1ColumnNumbers
        DestinationArray1(1, DestinationArray1ColumnNumber) = _
                DestinationWS.Cells(1, 10 + DestinationArray1ColumnNumber)                          '   Load Destination K1:Q1 headers into DestinationArray1
    Next                                                                                            ' Loop back
'
    DestinationWS.Range("K2:Q" & DestinationLastRow).Clear                                          ' Clear the range to be written to
'
'--------------------------------------------------------------------------------------------------
'
    ReDim DestinationArray2(1 To SourceRows, 1 To 17)                                               ' Set DestinationArray2 row and column size
'
    For DestinationArray2ColumnNumber = 1 To UBound(DestinationArray2, 2)                           ' Loop through DestinationArray2ColumnNumbers
        DestinationArray2(1, DestinationArray2ColumnNumber) = _
                DestinationWS.Cells(1, 25 + DestinationArray2ColumnNumber)                          '   Load Destination Z1:AP1 into DestinationArray2
    Next                                                                                            ' Loop back
'
    DestinationWS.Range("Z2:AP" & DestinationLastRow).Clear                                         ' Clear the range to be written to
'
'--------------------------------------------------------------------------------------------------
'
    For I = 1 To SourceLastColumnNumber                                                             ' Loop throug all columns of the source sheet
        Select Case SourceArray(1, I)                                                               '   Find a match to ...
            Case Is = "Date"                                                                        '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray1(J, 1) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Supplier Invoice Date"                                                       '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray1(J, 2) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Narration"                                                                   '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray1(J, 3) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Supplier Invoice No."                                                        '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray1(J, 4) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Voucher No."                                                                 '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray1(J, 5) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Particulars"                                                                 '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray1(J, 6) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Gross Total"                                                                 '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray1(J, 7) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
'
            Case Is = "Input CGST @ 2.5%"                                                           '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 1) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input SGST @ 2.5%"                                                           '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 2) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input CGST @ 6%"                                                             '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 3) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input SGST @ 6%"                                                             '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 4) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input CGST @ 9%"                                                             '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 5) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input SGST @ 9%"                                                             '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 6) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input CGST @ 14%"                                                            '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 7) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input SGST @ 14%"                                                            '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 8) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input IGST @ 5%"                                                             '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 9) = SourceArray(J, I)                                     '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input IGST @ 12%"                                                            '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 10) = SourceArray(J, I)                                    '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input IGST @ 18%"                                                            '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 11) = SourceArray(J, I)                                    '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Input IGST @ 28%"                                                            '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 12) = SourceArray(J, I)                                    '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Round Off"                                                                   '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 13) = SourceArray(J, I)                                    '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Purchase GST Local"                                                          '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 14) = SourceArray(J, I)                                    '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Purchase IGST Interstate"                                                    '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 15) = SourceArray(J, I)                                    '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Courier Charges @ 5%"                                                        '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 16) = SourceArray(J, I)                                    '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
'
            Case Is = "Cartage"                                                                     '       Source Header to match
                For J = 2 To SourceRows                                                             '           When match is found, Loop through the rows and
                    DestinationArray2(J, 17) = SourceArray(J, I)                                    '               Write the values to the DestinationArray
                Next                                                                                '           Loop back for next row
        End Select
    Next
'
'--------------------------------------------------------------------------------------------------
'
    DestinationWS.Range("K1:Q" & SourceRows) = DestinationArray1                                    ' Display results of DestinationArray1 to destination sheet
    DestinationWS.Range("Z1:AP" & SourceRows) = DestinationArray2                                   ' Display results of DestinationArray2 to destination sheet
'
    Application.ScreenUpdating = True                                                               ' Turn ScreenUpdating back on
'
MsgBox "  " & SourceRows & " Rows Processed in " & Timer - StartTime & "Seconds."                   ' Display the time it took to run the script
End Sub

 

[RAJESH1960]

OMG! That looks like a real long code. Let me try. Will check now

 

[RAJESH1960]

How about five stars for the code. It is perfect JohnnyL. Thank you very much. Another one hour of work in a second.❤❤

 

[johnnyL]

Glad to help.

P.S. Don't tell your boss, that gives you an hour to mess around and do other things.

 

[RAJESH1960]

I save time to learn more. I go to the office for only 1-2 hours only, finish my work which would could take normally 15 days to complete. I have just begun learning accounts. So whatever I am asked to do, mostly entering data into the server I try to find an easier way to finish the work with the help of applications which I have created with the help of these codes. I am enjoying my work so there is no place for complaints or getting more work.

 

[RAJESH1960]

The more tough jobs the merrier. It is like accepting challenging work.

 

[RAJESH1960]

You have solved 2 queries with this one. The next project has output in place of input. I will try to edit the code and finish that too. If required will message you.