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

**fixer** · 05-03-2020, 05:27 PM

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.,

**DocAElstein** · 05-03-2020, 09:45 PM

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
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	C	F	K
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

**fixer** · 05-03-2020, 10:35 PM

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

**DocAElstein** · 05-03-2020, 11:28 PM

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

**fixer** · 05-03-2020, 11:41 PM

**DocAElstein** · 05-04-2020, 02:20 AM

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

**fixer** · 05-04-2020, 03:17 AM

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

**DocAElstein** · 05-04-2020, 01:56 PM

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.

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