Code:
Sub COPYpaste()
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 Subafter runing the same macro it is pasting the data incorrectly plz see the file
i have converted 2.csv to 2.xlsx bcoz i was unable to upload it so plz convert the file from 2.xlsx to 2.csv
& then run the macro and see the details