Actions
Sick leave periods in one cell
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 so i need help for the same (plz ssee the correct output pic and make this macro to give correct output)
Plz have a look Sir and help me out in solving this problem Sir
Hi Sholtan
Sorry but i have no idea what you want, can you exaplian what the problem is in detail and what is your expect outcome? Can you also attach an example work book rather than just paste a load of code into the message?
That way we do have to recrated anything.
Purfleet
i have attached sample file
plz see the file and run the macro, everything is perfect with this code but it paste the data incorrectly to 2.csv
i have uploaded both the pics correct output and incorrect output
when i run the macro i should get the correct output but i am not getting the same in 2.csv
it’s the macro that I need uploading as the code that was pasted in is just text and in that format it is very difficult to read.
If the issue is that the data is copied over to the wrong sheet
I am guessing that there is an issue with one of the variables that has been defined (and there are a lot of variables in this code)
I would suggest stepping throuigh the code with f8 so you can see what is happening at each step and what workbooks or worksheets are being selected when the data is written back.
Purfleet
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
see now its readable Purfleet Sir
This code has pasting issue as i mentioned plz help me Purfleet Sir in solving this problem
Sholtan,
Please attach the workbook with the VBA in it. Even if you paste code we have to copy it, create a workbook, paste it in, save it etc etc. If there's then a problem is it because we did something wrong, or your code was pasted into the forum with errors?
If we have your code in a workbook we know that it's the same code you are using.
If you want us to help please make it easy for us.
Regards
Phil
Sholtan,
I can't figure out what files are for input,which ones are for output.
You've shown an image of 'correct output' but which file is this? Where do you get the data for the input?
I've looked at the files you supplied but can't see how you've come up with the output as you've specified.
Phil
vba will be placed in a seperate file macro.xlsm
i have three files 1.xls & 2.csv & 3.xlsx
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
If I understand this correctly you want to paste row 1 of sheet 3, in sheet 2 (the csv file) the number of rows equal to the rows in sheet 1?
Sorry, but I found your code really confusing - if the above summery is correct this should do the trick and is much easier to follow. You might have to change the file locations, I have them set as one on the Macro work sheet. And I didn’t know what you wanted doing with 2-1.csv once the data was copied over.
Sub Step1Ver2()
Dim LastRow As Integer
Dim Path As String
Path = ThisWorkbook.Sheets(1).Range("b1")
Workbooks.Open (Path & "\1-1.xls")
LastRow = Workbooks("1-1.xls").Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("1-1.xls").Close False
Workbooks.Open (Path & "\2-1.csv")
Workbooks.Open (Path & "\3-1.xlsx")
Workbooks("3-1.xlsx").Sheets(1).Range("1:1").Copy Workbooks("2-1.csv").Sheets(1).Range("a1:a" & LastRow) - 1
'Workbooks("2-1.csc").Close False
Workbooks("3-1.xlsx").Close False
End Sub
1 Guest(s)
Devices in use: Desktop (12), Phone (5)