Macro 1

[Austin5421]

Hey There

Sub COPYpaste()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
 Set w1 = Workbooks.Open("C:\Users\Desktop\sample1.xls")
 Set w2 = Workbooks.Open("C:\Users\Desktop\sample2.csv")
 Set w3 = Workbooks.Open("C:\Users\Desktop\sample3.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 = "='[sample3.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

I am trying to make a macro but i met with a problem so i am looking for help

So plz have a look and help me out

 

[IDS]

It's not at all clear what it is you are trying to do.

Could you add comments to each section of code, explaining what they are supposed to be doing?

Have you tried stepping through the code so you can see where it is going wrong?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Austin5421]

Sure Sir all details are give below
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
i have attached a sample pic plz have a look
sample1.xls is 1.xls
sample2.csv is 2.csv
sample3.xlsx is 3.xlsx

 

[Austin5421]

Instead of that i am geeting this after runing macro

 

[IDS]

How about the comments to the code?

What is this line supposed to do?:
Let Lc3Ltr = CL(Lc3)

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

See code below with comments.
Should be enough to get you going.

Sub COPYpaste()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
' Set w1 = Workbooks.Open("C:\Users\Desktop\sample1.xls")
Set w1 = Application.ActiveWorkbook
' Set w2 = Workbooks.Open("C:\Users\Desktop\sample2.csv")
' Set w3 = Workbooks.Open("C:\Users\Desktop\sample3.xlsx")
' I have moved all the worksheets to one file to make it simpler
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
 Set Ws1 = Application.Worksheets.Item(1)
 Set Ws2 = Application.Worksheets.Item(2)
 Set Ws3 = Application.Worksheets.Item(3)
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
' I don't know what CL does, I have hard-coded "K"
 Let Lc3Ltr = "K"  'CL(Lc3)
 Let Lenf1 = Lr1 - 1
Dim rngOut As Range
Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
' The second rngout doesn't work, and would overwrite the first one if it did
'Set rngOut = Ws2.Range("A1:" & Lc3 & Lenf1 & "")
 Ws2.Cells.NumberFormat = "General"
' why are there three Let statements to the same range?
 Let rngOut.Value = "='[sample1.xlsb]" & 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
' If you close the workbook with the macro, the macro will stop
' w1.Close
 w1.Save
' Let Application.DisplayAlerts = False
' w2.Close
' Let Application.DisplayAlerts = True
' w3.Close

End Sub

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Austin5421]

Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
getting error with this line

 

[Austin5421]

Sir if my macro contains many errors so plz make a new macro i am getting error i shared u al details
I am not a professional one

 

[IDS]

You didn't share details, you posted a screenshot of an error message. You haven't answered half the questions I have asked.

But the code I posted is a new macro anyway. Did you try running it?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Austin5421]

vba will be placed in a seperate file macro.xlsm
i have three files 1.xls & 2.csv & 3.xlsx
In 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

https://files.engineering.com/getfile.aspx?folder=43888ad4-b8e9-4572-97b8-3e72b1e51ac2&file=1.xls

https://files.engineering.com/getfile.aspx?folder=9948c57f-66bf-482b-802e-ffeff8c35117&file=2.xlsx

https://files.engineering.com/getfile.aspx?folder=1c16f661-798b-42e7-b2e1-d5b60023fc0c&file=3.xlsx

 

[Austin5421]

Yes i ran the macro Dis Sir & i mentioned the error details to u also

 

[IDS]

You didn't say which macro you were running when you got the error.

Did you create two new worksheets for file sample1?

What were the values of Lc3Ltr and Lenf1 when you got the error message?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Austin5421]

Sub COPYpaste()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
' Set w1 = Workbooks.Open("C:\Users\Desktop\sample1.xls")
Set w1 = Application.ActiveWorkbook
' Set w2 = Workbooks.Open("C:\Users\Desktop\sample2.csv")
' Set w3 = Workbooks.Open("C:\Users\Desktop\sample3.xlsx")
' I have moved all the worksheets to one file to make it simpler
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
 Set Ws1 = Application.Worksheets.Item(1)
 Set Ws2 = Application.Worksheets.Item(2)
 Set Ws3 = Application.Worksheets.Item(3)
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
' I don't know what CL does, I have hard-coded "K"
 Let Lc3Ltr = "K"  'CL(Lc3)
 Let Lenf1 = Lr1 - 1
Dim rngOut As Range
Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
' The second rngout doesn't work, and would overwrite the first one if it did
'Set rngOut = Ws2.Range("A1:" & Lc3 & Lenf1 & "")
 Ws2.Cells.NumberFormat = "General"
' why are there three Let statements to the same range?
 Let rngOut.Value = "='[sample1.xlsb]" & 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
' If you close the workbook with the macro, the macro will stop
' w1.Close
 w1.Save
' Let Application.DisplayAlerts = False
' w2.Close
' Let Application.DisplayAlerts = True
' w3.Close

End Sub

I just googled and i saw the macro similar to this i modifed little things so thas y i am unable to provide u the exact details of my macro
and i ran ur macro Dis Sir which u modified and provided to me

 

[IDS]

Did you create two new worksheets for file sample1?

You need to read and understand the comments I added to your code.

To avoid the complications of using three files I created two new sheets, but if you are using your original file with just one sheet it won't work when it tries to write to sheet 2.

But if you don't understand what is going on with the original code I really think you would be better to start with something simpler anyway.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Austin5421, you have been here before many times and at Tek-Tips, with vey similar questions.

We do not write code for novices. We will only give you Tips to improve your code.

We will not make a Macro for you. That's your job.

It is obvious that you are in deep water, way over your head and you don't know how to swim. We are not tutors.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Austin5421]

SkipVought I dont need ur tip
I already have a conversation with ids
whatever he suggest & will provide i will accept that
But SkipVought I dont need ur tips and suggestions

 

[Austin5421]

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 gives incorrect output dis sir plz see thes ample pic

 

[Austin5421]

the output should be

 

[SkipVought]

Yes, indeed, Austin5421. I've replied to stuff you posted under a different username as long ago as June of 2019, when you were booted from Tek-Tips, I believe. I just verified my suspicion.

Interestingly, you almost always have three files named 1, 2 & 3.

If any of you want proof that he's the same individual, you can reply to my faq766-2001, with a brief message. Management will send me an eMail with your eMail address, and I'll send you my proof. Don't want to tip off this pretender.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

Don't blame Skip. I had come to the same conclusions before he confirmed them.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/