Macro

Question

Macro

Austin5421 (Computer)

(OP)

5 May 20 07:42

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

IDS (Civil/Environmental) · Answer 1 · 2020-05-05 09:07:12

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 (Computer) · Answer 2 · 2020-05-05 09:42:18

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 (Computer) · Answer 3 · 2020-05-05 09:46:29

Instead of that i am geeting this after runing macro

IDS (Civil/Environmental) · Answer 4 · 2020-05-05 10:29:55

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 (Civil/Environmental) · Answer 5 · 2020-05-05 10:55:34

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/

Austin5421 (Computer) · Answer 6 · 2020-05-05 11:20:34

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

Austin5421 (Computer) · Answer 7 · 2020-05-05 12:41:34

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 (Civil/Environmental) · Answer 8 · 2020-05-05 22:23:48

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 (Computer) · Answer 9 · 2020-05-06 07:25:58

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=...

https://files.engineering.com/getfile.aspx?folder=1c16f661-798b-42e7-b2e1-d

Austin5421 (Computer) · Answer 10 · 2020-05-06 09:09:12

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

IDS (Civil/Environmental) · Answer 11 · 2020-05-06 09:23:27

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 (Computer) · Answer 12 · 2020-05-06 09:30:17

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

IDS (Civil/Environmental) · Answer 13 · 2020-05-06 12:00:29

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 (Aerospace) · Answer 14 · 2020-05-06 15:13:12

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,
_{Just traded in my OLD subtlety...

for a NUance!}

Answer 15

[Post Deleted]

Answer 16

[Post Deleted]

Answer 17

[Post Deleted]

Austin5421 (Computer) · Answer 18 · 2020-05-06 15:39:33

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 (Computer) · Answer 19 · 2020-05-06 20:03:16

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

Austin5421 (Computer) · Answer 20 · 2020-05-06 20:04:19

the output should be

SkipVought (Aerospace) · Answer 21 · 2020-05-06 20:53:48

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,
_{Just traded in my OLD subtlety...

for a NUance!}

IDS (Civil/Environmental) · Answer 22 · 2020-05-06 21:58:29

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

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

IRstuff (Aerospace) · Answer 23 · 2020-05-06 22:29:25

If this is not for work; this thread will likely be deleted

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

Austin5421 (Computer) · Answer 24 · 2020-05-07 06:03:23

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

Austin5421 (Computer) · Answer 25 · 2020-05-07 06:05:58

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

Agent666 (Structural) · Answer 26 · 2020-05-07 08:03:50

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).

Austin5421 (Computer) · Answer 27 · 2020-05-07 08:44:42

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

SkipVought (Aerospace) · Answer 28 · 2020-05-07 16:15:46

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

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

IRstuff (Aerospace) · Answer 29 · 2020-05-07 18:51:25

Thanks; it must says something that my email client put it in the junk folder...

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

Come Join Us!

Posting Guidelines

Macro

Macro

Macro

CODE

Recommended for you

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

CODE --> VBA

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

CODE -->

RE: Macro

RE: Macro

RE: Macro

RE: Macro

CODE -->

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

RE: Macro

Red Flag This Post

Red Flag Submitted

Reply To This Thread

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

News

Join Eng-Tips® Today!

Join Eng-Tips^® Today!