Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

Macro 1

Status
Not open for further replies.
Austin5421

Austin5421

Computer
May 5, 2020
14
0
0
IN
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
sample2_jnkdxl.png
Sample3_bqottp.png



So plz have a look and help me out
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
Before_rgiofc.png
After_runing_Macro_kjxyiq.png
 
Upvote 0 Downvote
See code below with comments.
Should be enough to get you going.

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

Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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

Capture_lvt0w3.png


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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
Capture_rlqnaf.png
 
Upvote 0 Downvote
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Gabriele82
  • Locked
  • Question
Macro catia
Replies
0
Views
8
Gabriele82
Gabriele82
pieswt
  • Locked
  • Question
Excel Macro File getting corrupted 1
Replies
3
Views
10
MintJulep
MintJulep
spigor
  • Locked
  • Question
Word 97 Update: Macro Vulnerability
Replies
4
Views
5
spigor
spigor
rk_19
  • Locked
  • Question
Invoke an .exe file using macro in excel
Replies
19
Views
20
rk_19
rk_19
rk_19
  • Locked
  • Question
macro recording
Replies
1
Views
2
SkipVought
SkipVought
Back
Top