×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Macro

Macro

(OP)
Hey There

CODE

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

RE: Macro

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/

RE: Macro

(OP)
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


RE: Macro

(OP)

Instead of that i am geeting this after runing macro

RE: Macro

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/

RE: Macro

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

CODE --> VBA

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/

RE: Macro

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

RE: Macro

(OP)
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

RE: Macro

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/

RE: Macro

(OP)
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=...
https://files.engineering.com/getfile.aspx?folder=...
https://files.engineering.com/getfile.aspx?folder=...

RE: Macro

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

RE: Macro

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/

RE: Macro

(OP)

CODE -->

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

RE: Macro

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/

RE: Macro

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro

(OP)
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

RE: Macro

(OP)

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

RE: Macro

(OP)
the output should be

RE: Macro

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: Make My Code Run Faster, 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro

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

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Macro

(OP)
No bro I have a Great respect for vba coders
But this is my first time in engtips & i never ever registered and asked anything on tektips

RE: Macro

(OP)
I have mentioned the details
plz see this post
6 May 20 20:03
6 May 20 20:04
i mentioned the problem
this code is pasting the data incorrectly to 2.csv

RE: Macro

Step through your code and identify which line combines all your values to give the wrong output. Use breakpoints and watches to review the values. I can't quite tell as way you've done it looks quite foreign to me. You didn't include your workbooks to enable anyone to run the code as you intended.

Its not the way I would do it, I'd read all the information into arrays, and write/process it to a VBA array with your final layout required. Then write the entire array back onto the sheet in one go. Copying and pasting things like you are doing shouldn't be required (really inefficient).



RE: Macro

(OP)
Sure Bro i have uploaded my sample files and details to u
plz see the pic that i have attached

RE: Macro

@IRsruff, sent another eMail that has missing pic. Sorry blush

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close