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

 

[IRstuff]

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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Austin5421]

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]

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]

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]

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

 

[SkipVought]

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

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

 

[IRstuff]

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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm