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
COPY AND PASTE
-
jhas56788 -
May 1, 2020 at 5:40 AM -
Thread is marked as Resolved.
-
-
-
You've provided 2.xlsx not csv.
Where are the files located or do you need to browse for each file?
-
Yes Roy Sir i have uploaded 2.xlsx bcoz i was unable to upload 2.csv in this forum so kindly convert 2.xlsx to 2.csv and then run a macro of the same
1.xls is located in C:\Users\WolfieeeStyle\Desktop\Files\1.xls
2.csv is located in C:\Users\WolfieeeStyle\Desktop\2.csv
3.xlsx is located in C:\Users\WolfieeeStyle\Desktop\save it\3.xlsx
-
Roy Sir u need more information then plz let me know
-
Problem Solved
-
-
Please share the solution in case it helps someone else.
-
Code
Display MoreSub 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 Roy Sir i met with an error Problem is not solved
issue is with this line
It paste the data column A only Plz help me in solving the same Sir
-
There's nothing wrong with that line of code.
What error do you get?
-
Plz see Roy Sir
-
-
-
I GOT THE CODE FROM GOOGLING AND SEEING MANY VARIATIONS OF THE VBA CODE AND I MADE THE SAME
plz correct the code so that it doesnt get any errors
-
-
Code
Display MoreSub 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 gives output but in column A plz see the sample pic all data is pasted to column A only
-
the output should be
-
If u r unable to solve this problem Roy Sir then No issue atleast let me know
I will break this process into two pieces
& i will start a new thread to solve this problem in a different way & plz do not close that post bcoz if its not solvable then i have to think & solve it in a different way
-
-
I won't have time to look at this before tomorrow.
-
No issues take ur time Roy Sir
-
Problem Solved
-
How did you solve it?
-
One of my Frnd helped me for the same
-
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!