VBA Copy Rows From One Workbook To text csv File Based On Count In Different Workbook. Cross Posted Chaos

Moderator notice.
This is the start of duplicate cross posting chaos
2 months latter he is still wasting his and lots of peoples times going around in circles making a total mess in posts everywhere because he refuses to understand anything at all about Text files.
The biggest problem is in using an Excel object to open a .csv File, which is usually not a good idea.
Sometimes you might get the impression he is understanding at least a small part of the problem, but wither it is just co incidence that what he has pasted infers that and he has no idea what he is writing, or two seconds later he forgets and we are back to the starting point and he starts again duplicating cross posting the same question… - I think there is a good chance the OP is insane, and certainly a total dim pig shit for brains. Physical violence is I think the only hope for him, and if all else fails then he should be put to death.,

---

vba will be placed in a seperate file macro.xlsm

i have three files 1.xls & 2.csv & 3.xlsx

1.xls first row has headers so dont count that

In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that much time of 3.xlsx first row of sheet3 to 2.csv

suppose 1.xls has data in 5 rows then copy 3.xlsx first row of sheet3 and paste it to 2.csv 5 times

all files are located in a different path

sheet name can be anything

plz see the sample file


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Remember to always please give a link to everywhere else that you post, for example
http://www.eileenslounge.com/viewtopic.php?f=30&t=34508 http://www.eileenslounge.com/viewtopic.php?f=30&t=34508

If you have posted anywhere else then please tell us where

---

In the macro I have done for you , there are two possibilities.
You only need one
You can choose
' 2b)(i) Relative formula references ...

Code:

---

' 2b)(i)  Relative formula references  ... https://teylyn.com/2017/03/21/dollarsigns/#comment-191
 Ws2.Cells.NumberFormat = "General"                          ' May be needed to prevent formulas coming out as test    =[3.xlsx]Sheet1!$A$1
 Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
 Let rngOut.Value = rngOut.Value  '  Change Formulas to values
 Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")      '                              https://excelribbon.tips.net/T010741_Removing_Spaces
' Or
'' 2b)(ii) Copy paste
'Dim rngIn As Range
' Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
' rngIn.Copy
' rngOut.PasteSpecial Paste:=xlPasteValues  '  understanding  Paste  across ranges of different size to  Copy  range : https://excelfox.com/forum/showthread.php/2221-VBA-Range-Insert-Method-Code-line-makes-a-space-to-put-new-range-in?p=10441&viewfull=1#post10441

---

OR
' 2b)(ii) Copy Paste

Code:

---

' 2b)(i)  Relative formula referrences  ... https://teylyn.com/2017/03/21/dollarsigns/#comment-191
' Ws2.Cells.NumberFormat = "General"                          ' May be needed to prevent formulas coming out as test    =[3.xlsx]Sheet1!$A$1
' Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
' Let rngOut.Value = rngOut.Value  '  Change Formulas to values
' Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")      '                              https://excelribbon.tips.net/T010741_Removing_Spaces
' Or
' 2b)(ii) Copy Paste
Dim rngIn As Range
 Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
 rngIn.Copy
 rngOut.PasteSpecial Paste:=xlPasteValues  '  understanding  Paste  across ranges of different size to  Copy  range : https://excelfox.com/forum/showthread.php/2221-VBA-Range-Insert-Method-Code-line-makes-a-space-to-put-new-range-in?p=10441&viewfull=1#post10441

---

Before:
_____ Workbook: 3.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L
1	NSE		6			A					GTT
2

Worksheet: Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1182	1193	1151.7	1190.45	1156.6	22	11.566	116815	1168.166
3	NSE	ADANIENT	EQ	137.15	140.55	134.1	140.5	134.65	25	1.3465	13595	135.9965
4	NSE	ADANIPORTS	EQ	273.95	276.95	269.55	277.6	270.65	15083	2.7065	27335	273.3565
5	NSE	ADANIPOWER	EQ	32.3	32.35	30.45	32.45	30.65	17388	0.3065	3095	30.9565
6	NSE	AMARRAJA	EQ	555	555	529.25	557.85	532.1	100	5.321	5374	537.21
7

Worksheet: 1-Sheet1 3Mai

_____ Workbook: 2.csv ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J
1
2
3

Worksheet: 2


After running macro here : https://excelfox.com/forum/showthrea...ll=1#post13184

_____ Workbook: 2.csv ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L
1	NSE		6			A					GTT
2	NSE		6			A					GTT
3	NSE		6			A					GTT
4	NSE		6			A					GTT
5	NSE		6			A					GTT
6

Worksheet: 2



Alan

---

Share ‘1.xls’ : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
Share ‘macro.xlsm’ : https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt
Share ‘2.csv’ : https://app.box.com/s/sop0hl5slstjv38tr8wtvp7f7l39ep3k
Share ‘3.xlsx’ : https://app.box.com/s/8k9b556duom523b381m80oeixonp0042

---

Edit ... cross posted later once again!!!!
https://chandoo.org/forum/threads/copy-and-paste.44182/
https://excelfox.com/forum/showthrea...ll=1#post13439

Thnx Alot Doc Sir for showing ur Great Interest in this post
Sorry for not giving a link bcoz i thought no one is working on this problem so Time will not be wasted of anyone
I will take care of the same in future

Code:

---

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
 Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
 Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\3.xlsx")
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
 Set Ws1 = w1.Worksheets.Item(1)
 Set Ws2 = w2.Worksheets.Item(1)
 Set Ws3 = w3.Worksheets.Item(1)
Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
 Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
 Let Lc3Ltr = CL(Lc3)
 Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
 Ws2.Cells.NumberFormat = "General"
 Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
 Let rngOut.Value = rngOut.Value
 Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
 Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
 rngIn.Copy
 rngOut.PasteSpecial Paste:=xlPasteValues
 w1.Close
 w2.Save
 Let Application.DisplayAlerts = False
 w2.Close
 Let Application.DisplayAlerts = True
 w3.Close

End Sub

---

i used this code Doc Sir
plz see the result i am getting i have uploaded the pic of the same
So plz relook in the same sir




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

What does 2.csv look like before you save and close it
What does this do:

Code:

---

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
 Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
 Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\3.xlsx")
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
 Set Ws1 = w1.Worksheets.Item(1)
 Set Ws2 = w2.Worksheets.Item(1)
 Set Ws3 = w3.Worksheets.Item(1)
Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
 Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
 Let Lc3Ltr = CL(Lc3)
 Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
 Ws2.Cells.NumberFormat = "General"
 Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
 Let rngOut.Value = rngOut.Value
 Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
 Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
 rngIn.Copy
 rngOut.PasteSpecial Paste:=xlPasteValues

End Sub

---

What does 2.csv look like after running above macro

I have uploaded the pic plz have a look sir
all the data is pasting to column A in one cell


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=320960#p320960
https://eileenslounge.com/viewtopic.php?p=320957#p3209573
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

This may be a complicated issue. ( I also said this here: ( https://excelfox.com/forum/showthrea...ll=1#post13150 ) )
This may be an issue with csv and Excel.
Your Excel will do csv different to my Excel. It is a very complicated issue. In English it is difficult to explain.
To explain to you will be almost impossible.

Your Excel may be doing different to my Excel with csv.
Maybe it is related to this question and problem : http://www.eileenslounge.com/viewtop...=34497#p267711

---

I would not manipulate csv files with Excel. I would do it like with Read and write files to text files
( https://www.homeandlearn.org/open_a_...le_in_vba.html
https://www.homeandlearn.org/write_to_a_text_file.html )

---

I am not sure if I can help you further.

I would need to see your csv file.
I would need to know exactly what you want to do

I think the communication problem between us would make it impossible to take this issue further.
The problem is too complicated for your understanding of English.

Alan

we have to use Chr(160) i think so
Plz have a relook Doc Sir
it should copy the complete row and it should paste the same
see the pic in capture.png which we are copying
and see the data in result pic how it is pasted

Once again …
This may be a complicated issue. ( I also said this here: ( https://excelfox.com/forum/showthrea...ll=1#post13150 ) )
This may be an issue with csv and Excel.
Your Excel will do csv different to my Excel. It is a very complicated issue. In English it is difficult to explain.
To explain to you will be almost impossible.

Your Excel may be doing different to my Excel with csv.

---

Quote:

---

Originally Posted by fixer

we have to use Chr(160) i think so

---

I do not understand what Chr(160) is. I do not understand how to use it. To use Chr(160) here may be an issue with csv and Excel.
Your Excel will do csv different to my Excel. It is a very complicated issue. In English it is difficult to explain.
To explain to you will be almost impossible.

---

I myself, I would not manipulate csv files with Excel. I would do it like with Read and Write files to text files
( https://www.homeandlearn.org/open_a_...le_in_vba.html
https://www.homeandlearn.org/write_to_a_text_file.html )

---

I am not sure if I can help you further.

I would need to see your csv file.
I would need to know exactly what you want to do

I think the communication problem between us would make it impossible to take this issue further.
The problem is too complicated for your understanding of English.
This would take me many days , if I have the time later, in a few days.

I will need to
Have two .csv files
_ One for Before
_ One for After
They must be .csv file. ( Maybe try GoogleDrive , app.box.com or other file sharing service.)
I must have files . csv files for Before and After

Screenshots are no use.

---

As always, the VBA problem and solution may be simple
The big problem is communication between us.
I mostly waste my time writing , because you understand almost nothing that I say !!


Alan

Before csv file link https://drive.google.com/open?id=1MF...s6EWCLjkblGxfo
after runing macro csv link https://drive.google.com/open?id=1V_...S63idSd5zlDcVX

there is chr(9) not chr(160)

Sandy Sir can u share the complete code with correction
bcoz i am confused with this code, i tried to make many changes but i was not successful
So its my request i am sending u the code plz have a look in it and plz let me know about the correction or correct the code and share the same
i am using this code

Code:

---

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
 Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
 Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\3.xlsx")
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
 Set Ws1 = w1.Worksheets.Item(1)
 Set Ws2 = w2.Worksheets.Item(1)
 Set Ws3 = w3.Worksheets.Item(1)
Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
 Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
 Let Lc3Ltr = CL(Lc3)
 Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
 Ws2.Cells.NumberFormat = "General"
 Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
 Let rngOut.Value = rngOut.Value
 Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
 Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
 rngIn.Copy
 rngOut.PasteSpecial Paste:=xlPasteValues
 w1.Close
 w2.Save
 Let Application.DisplayAlerts = False
 w2.Close
 Let Application.DisplayAlerts = True
 w3.Close

End Sub

---

.

sorry but no. I am away from vba.
I checked csv only, that's all

Code:

---

Ws2.Cells.NumberFormat = "General"

---

I changed this to

Code:

---

Ws2.Cells.NumberFormat = Chr(9)

---

But i am not getting correct output sandy Sir

chr(160) or chr(9) are delimiters not a format

Sorry for the same
Plz let me know sandy sir what correction i want to do

My dear friend, as I said I am not able to help you with vba
You need to describe your problem with details, what you want to do, post representative example (excel file and source(s) )what you have and what is your expected result
then maybe any master of vba will help you %D

Problem is already described in the post sandy sir plz relook

Attachment 2895
is that what you want?

note: remove .txt to get result.csv only

Bro i am not getting what u mean to say i tried to correct it from many ways but i was unsuccessful
if there is any changes or correction plz correct it and share
i am not having enough knowledge of vba tp solve this problem

I am not talking about vba but asking question: the file i posted above is in format you want to achieve?

Plz see the sample pic Sir
i need this result after runing the macro AFTER RUNING THE MACRO.PNG Sir

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984
https://eileenslounge.com/viewtopic.php?f=30&t=41610
https://eileenslounge.com/viewtopic.php?p=322176#p322176
https://eileenslounge.com/viewtopic.php?p=322238#p322238
https://eileenslounge.com/viewtopic.php?p=322270#p322270
https://eileenslounge.com/viewtopic.php?p=322300#p322300
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

	B	C	D	E	F	G	H	I	J	K	L
2	NSE		6			A					GTT
3	NSE		6			A					GTT
4	NSE		6			A					GTT
5	NSE		6			A					GTT
6	NSE		6			A					GTT

I forgot to remove last column with numbers
Attachment 2899

this will be better because doesn't matter place where csv will be after open in Excel

NSE		6			A					GTT
NSE		6			A					GTT
NSE		6			A					GTT
NSE		6			A					GTT
NSE		6			A					GTT

out of curiosity: why result in csv format?

Bro what i have to do with this result.csv.txt
?

Code:

---

Sub Step14()    '    http://www.eileenslounge.com/viewtopic.php?f=30&t=34508 (zyxw123)    https://excelfox.com/forum/showthread.php/2467-COPY-AND-PASTE?p=13182#post13182
Rem 1 Worksheets info
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
 Set w1 = Workbooks("1.xls")                        ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xlsx")
 Set w2 = Workbooks("2.csv")                        ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\document\2.csv")
 Set w3 = Workbooks("3.xlsx")                        ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\files\3.xlsx")
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
 Set Ws1 = w1.Worksheets.Item(1)
 Set Ws2 = w2.Worksheets.Item(1)
 Set Ws3 = w3.Worksheets.Item(1)
Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row      '  http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
 Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
 Let Lc3Ltr = CL(Lc3)
Rem 2  ' In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that much time of 3.xlsx first row of sheet3 to 2.csv
 Let Lenf1 = Lr1 - 1                                                ' 1.xls first row has headers so dont count that
' 2a)
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
' 2b)(i)  Relative formula referrences  ... https://teylyn.com/2017/03/21/dollarsigns/#comment-191
 Ws2.Cells.NumberFormat = "General"                          ' May be needed to prevent formulas coming out as test    =[3.xlsx]Sheet1!$A$1
 Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
 Let rngOut.Value = rngOut.Value  '  Change Formulas to values
 Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")      '                              https://excelribbon.tips.net/T010741_Removing_Spaces
' Or
' 2b)(ii) Copy Paste
Dim rngIn As Range
 Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
 rngIn.Copy
 rngOut.PasteSpecial Paste:=xlPasteValues  '  understanding  Paste  across ranges of different size to  Copy  range : https://excelfox.com/forum/showthread.php/2221-VBA-Range-Insert-Method-Code-line-makes-a-space-to-put-new-range-in?p=10441&viewfull=1#post10441

Rem 3
' w1.Close
' w2.Save
' Let Application.DisplayAlerts = False
' w2.Close
' Let Application.DisplayAlerts = True
' w3.Close
'
End Sub

---

This is the vba code Doc Sir gave me
Now plz have a look into this code and if any changes are required then plz let me know

change result.csv.txt to result.csv and open with eg.Excel
btw. csv is NOT a native Excel format but IBM

I added txt on the end because this forum don't accept csv files

edit:
csv = comma separated values

I dont need csv files i need a macro that will do the process and i will get the result what i am looking for
i have a vba code that is slightly different but that code has pasted the data to csv i am sharing the same
dont get confuse i am sharing the code only to understand the probelm and solve this problem

Code:

---

Sub STEP3()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim wb3 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim strPath As String
    Dim R As Long
    Dim m As Long
    Dim rng As Range
    Dim n As Long
    Application.ScreenUpdating = False
    Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
    Set ws1 = wb1.Worksheets(1)
    m = ws1.Range("H" & ws1.Rows.Count).End(xlUp).Row
    strPath = ThisWorkbook.Path & "\"
    Set wb2 = Workbooks.Open(strPath & "OrderFormat.xlsx")
    Set ws2 = wb2.Worksheets(1)
    ws2.Range("A1:A4").TextToColumns DataType:=xlDelimited, Tab:=True, _
    SemiColon:=False, Comma:=False, Space:=False, Other:=False, _
    ConsecutiveDelimiter:=False
    Set wb3 = Workbooks.Open(strPath & "BasketOrder..csv")
    Set ws3 = wb3.Worksheets(1)
    Set rng = ws3.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If rng Is Nothing Then
        n = 1
    Else
        n = rng.Row + 1
    End If
    For R = 2 To m
        If ws1.Range("H" & R).Value > ws1.Range("D" & R).Value Then
            ws2.Range("A2").EntireRow.Copy Destination:=ws3.Range("A" & n)
            n = n + 1
        ElseIf ws1.Range("H" & R).Value < ws1.Range("D" & R).Value Then
            ws2.Range("A4").EntireRow.Copy Destination:=ws3.Range("A" & n)
            n = n + 1
        End If
    Next R
    Application.DisplayAlerts = False
    wb1.Close SaveChanges:=False
    wb2.Close SaveChanges:=False
    wb3.SaveAs Filename:=strPath & "BasketOrder..csv", FileFormat:=xlCSV
    wb3.Close SaveChanges:=False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

---

sure, so I can't help
I did what you said in the first post without vba
have a nice day

That's Great Bro I like that & thnx for the same
Bro but i need that to be done by vba code this is my question & this is i am looking for
Thnx for ur Great Help & I like ur Attempt to solve this Problem
Relax Bro It will take time but i will resolve this problem & i will share the code with u
Have a Great Day

Doc Sir u have mentioned some code in this link https://excelfox.com/forum/showthrea...ll=1#post13184
i saw that but i am confused what i have to do now with this problem what exactly the code should be as per u

I am trying to answer your question. I am working on it - I am developing a solution in the Development Test Forum at that link
Wait

Hi Sandy ;)
Thank you Mr Sandy, Sir, for your interesting contribution to this Thread.,
I expect vixer has not understood much, due to his appalling grip on the English language, but it has given me some interesting insights into the issues involved.

I have also tried to explain to vixer that we are dealing with issues of Excel and how it handles files of text / csv etc type.
I am not too clued up on these issues, and vixer usually has no idea half the time of what the hell is going on at the best of times when he is only looking at Excel VBA issues. As he is now including extra issues to do with csv / txt files , it is anyone’s guess what he is wanting. I doubt very much he knows himself very clearly. Coupled with his inability to explain what he wants in English, it is anyone’s guess what is wanted here in this Thread.

Never mind.
In the next post I will try to take the Thread a bit further.
Then I will go off and hide in a hole in my garden, Chisel out a few more rocks, ( https://imgur.com/8mV1o8q ) , and pull some of my teeth out for relaxation.

Then tomorrow I will look in again here to see what’s happening

Alan

Hello Vixer
As always, the main problem is that you either cannot explain what you want, or don’t understand yourself what you want or what you are doing. Probably a bit of both.

Never mind, stay cool Bro, an lets move on…. :)

I have tried to do some analysis of your 2 supplied “csv files” Before and After , here : https://excelfox.com/forum/showthrea...ll=1#post13208

I am probably wasting my time trying to explain what Sandy also is trying to explain…
csv means
comma separated values
__ , __ separated values
https://en.wikipedia.org/wiki/Comma-separated_values
Your supplied files ( From here: https://excelfox.com/forum/showthrea...ll=1#post13193 https://drive.google.com/file/d/1MFI...LjkblGxfo/view https://drive.google.com/file/d/1V_8...Sd5zlDcVX/view ) are not csv files
I do not know what they are. Maybe we can call them Tab delimited or Tab separated. Or we can call them dog shit files

---

I will now modify my last macro so that it makes the DogShit file that you want

The macro will produce two files:
2.csv
DogShit.txt

We do not open 2.csv or DogShit.txt … We make them !
Do not open 2.csv in the macro!!!! Do not put a code line in the macro to open 2.csv!!! ( You can open manually later with text editor or Excel )

Here is the macro : https://excelfox.com/forum/showthrea...ll=1#post13217
After you run that macro , you will see two new files:
New made Dog Shit files.JPG : https://imgur.com/UUZ09z6
Attachment 2902

Those two files, which my latest macro produces, are identical to your supplied After csv file
After csv.JPG : : https://imgur.com/IzaxRrh
( https://drive.google.com/file/d/1V_8...Sd5zlDcVX/view )
Attachment 2903


I must go now , dig a big hole and pull some teeth out for relaxation ( https://imgur.com/8mV1o8q ) ,
Tomorrow I will look in again here ….

Alan

---

Share ‘1.xls’ : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
Share ‘macro.xlsm’ : https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt
Share ‘3.xlsx’ : https://app.box.com/s/8k9b556duom523b381m80oeixonp0042

Quote:

---

Originally Posted by DocAElstein

Hi Sandy ;)
Thank you Mr Sandy, Sir, for your interesting contribution to this Thread.,
I expect vixer has not understood much, due to his appalling grip on the English language, but it has given me some interesting insights into the issues involved.

---

Hi Mr Alan Sir ;)
I gave up %p
---
I did it with M-code but I really don't know what fixer want as the result :confused:

Code:

---

// Sheet1
let
    Source = Excel.Workbook(File.Contents("D:\test\csvs\3.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    List = Table.AddColumn(Sheet1_Sheet, "Custom", each {1..CNT}),
    Expand = Table.ExpandListColumn(List, "Custom"),
    RC = Table.RemoveColumns(Expand,{"Custom"})
in
    RC

// CNT
let
    Source = Excel.Workbook(File.Contents("D:\test\csvs\1.xls"), null, true),
    #"1-Sheet2" = Source{[Name="1-Sheet1"]}[Data],
    Promote = Table.PromoteHeaders(#"1-Sheet2", [PromoteAllScalars=true]),
    Count = Table.RowCount(Promote)
in
    Count

---

I want this in a csv file not in a notepad
I am looking for automation of the same
Why we should waste our time to transfer data from notepad to excel
Its not a new thing i am asking for i already have a macro who paste the data to same csv file but it is slightly different
I think lets make it simple
the work of macro is counting and pasting the data to csv not more then that its not a new thing
I am not that much educated so i cant solve it but u can solve
the same (as sandy said masters of vba)

csv is a text file with comma separated values. it can (but not must - this is not mandatory) be opened in notepad (text file) or Excel or any other program which accept this kind of file
as I said before: csv is NOT native format of Excel

it's a bit weird you want create csv from Excel then open this csv in Excel. Would be better to get the result directly in Excel, isn't it?

FYI: CSV

I completely agree with all terms and conditions u r saying
I only mean to say that in csv file can we use the macro to copy & paste?
1)If yes then for this problem we can do the same
2) If No then I have a macro that has pasted the data to csv (slighlty different from this problem)
3)one more thing no doubt it was a csv file i have save that file in csv (If u have a doubt that it was not a csv file or any other xyz file then u can create a csv file and take 1.xls &3.xlsx from the post and run the macro and see the result

Let's leave this topic
We will solve the problem in a different way
Thnx Sandy Sir & Doc Sir for helping me in solving this problem
Have a Great Day Bro

what does that mean: in csv file can we use the macro to copy & paste ?
to me it looks like any vba code inside csv file, like generate vba from vba to csv
usually file with vba is a .bas file (also in text format, which not means it must be open in notepad)

I generated csv with the result like you want, it could be open in Excel with data format you want. Of course I did it without vba so Alan will be better to create vba for this

if you want csv , generate the result you want by vba in Excel then use vba to save this excel as csv

So plz provide me that line so that i will put the same and it will be solved sandy sir

Quote:

---

Originally Posted by fixer

So plz provide me that line so that i will put the same and it will be solved sandy sir

---

if you want do this via vba you need to wait for Alan, or someone else