• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

copy and paste

r@1234

Member
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
 

Attachments

vletm

Excel Ninja
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

Member
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
 

Attachments

vletm

Excel Ninja
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

Member
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

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

Attachments

r@1234

Member
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

Excel Ninja
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

Member
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
Code:
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


Code:
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
 

Attachments

vletm

Excel Ninja
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

Member
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
 
Top