copy and paste

[r@1234]

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 seperate file
sheet name can be anything
plz see the sample pic

 

[vletm]

r@1234
Please reread Your writing ...
eg1 all files are located in a seperate file
eg2 sheet name can be anything ... You already wrote something else.
... and ...
Your 2csv(result).PNG -photo is not from 2.csv -file (it has taken from Excel).
Upload sample Excel and CSV-files
as well as
those files after 'copy and paste' ( =expected results with Your data).

 

[r@1234]

Your 2csv(result).PNG -photo is not from 2.csv -file (it has taken from Excel).
How u know it can u let me know Sir
plz see the sample file vletm Sir

 

[vletm]

Your 2csv(result).PNG -photo is not from 2.csv -file (it has taken from Excel).
How u know it can u let me know Sir
I could see left side numbers, top letters and gridlines.
Your 2.csv has text CONVERT THIS FILE TO CSV BCOZ I WAS UNABLE TO UPLOAD THE CSV FILE SO I CONVERTED IT INTO XLSX
Do that mean? ... Your real CSV-file could be different?
If You have an idea to use CSV-files then ... You should able to give same kind of file as You'll use - otherwise - it could work or something else.

Did You noticed:
as well as
those files after 'copy and paste' ( =expected results with Your data).?
I did not noticed expected results!

 

[r@1234]

CONVERT THIS FILE TO CSV BCOZ I WAS UNABLE TO UPLOAD THE CSV FILE SO I CONVERTED IT INTO XLSX
ignore this bcoz i already uploaded the csv file

 

[r@1234]

Csv file contains result

 

[vletm]

r@1234
I understood ignore this
I'll ignore this too, because I don't start digging third time details, which You need.

 

[r@1234]

I don't start digging third time details, which You need.
Means
u have any doubts now Sir?

 

[r@1234]

Problem Solved

 

[r@1234]

all files are located in a seperate path (different folder)
Now i got ur point Bro
Have a Great Day

 

[r@1234]

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 = "K"
 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

this code is giving this result that i have attached plz see sir

 

[r@1234]

the result should be

 

[r@1234]

plz correct the code sir i am unable to correct it

 

[r@1234]

If u r unable to solve this Problem Sir
Then it's my job to think a different strategy in solving this problem
So that's y i created that post & u closed that post No issues
but if this problem is not solved then i have different strategy to solve
& i will solve this differently & for that i have to create a new post bcoz i cant mix two question in 1 post
So plz have a look & help me out for the same
& if i broke ur forum rules then sorry for the same
I just told u what is going on in my head

 

[vletm]

r@1234
Please, try to write full words, then many of Your text would be more readable.
I have already asked to send needed files (#2 reply),
which show what do You would like to get.
You have not done that.
Without correct information, You can only wait.
You can any time reread Forum Rules and get there more hints - how to get results?

 

[r@1234]

Thnx Alot Bro & Sorry for ignorance from my side but i was not aware that u r asking for files
i have uploaded the files

Sub Macro()
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 = "K"
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

This Macro works perfect
i am facing issue with saving of the file of 2.csv

w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close

w1 & w3 are perfect it will be closed by the code
but when i try to save w2 it saved as text(tab delimited) & i dont want that in the macro i want to save 2.csv in csv format only not in text(tab delimited)
So plz help me in solving the same

 

[vletm]

r@1234
I tried many times.
Seems that You skipped my previous reply
as well as what I have asked.
... and I won't start to thinking to modify others code.
Take care.

 

[r@1234]

Sorry for the same its a writing habit it will take time
If u r interested in solving this problem then fine & if not then
Have a Great Day Tc bro

 

[r@1234]

Problem Solved
Thnx VletM