Krishnan's Profile
Top Contributor
926
Points

Questions
1

18

• Top Contributor Asked on May 17, 2018 in

• 169 views
• Top Contributor Asked on May 17, 2018 in

• 169 views
• Top Contributor Asked on May 17, 2018 in

Dear sir

Still have one more writing problem, it is taken only first rown of the invoice, second row is not taken.  Also first two columns are not getting filled

Sub Macro2()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim oc As Range
Dim ocd As Range
Dim WH As Range
Dim x As Range
Dim rw As Long
Dim rng As Range
Dim fin As Range
Dim i As Integer
Set ws1 = Worksheets("oc invoice")
Set ws2 = Worksheets("OC DETAILS")
Set ws3 = Worksheets("oc finance")
Set oc = ws1.[invbno]
Set ocd = ws1.[ocdt]
Set WH = ws1.[B9]
Set fin = ws1.[d20]
Set x = ws1.Range("k:k")
rw = Application.WorksheetFunction.CountA(x) + 4
Set rng = ws1.Range("a11:k" & rw)
Application.ScreenUpdating = False
ws2.Activate
oc.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
ocd.Copy Destination:=ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1)
rng.Copy
ws2.Cells(Rows.Count, 4).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Cells(Rows.Count, 1).End(xlUp).Select
If ActiveCell.Offset(0, 11) = "" Then GoTo Line1
ws2.Cells(Rows.Count, 11).End(xlUp).Offset(0, -10).Select
For i = 1 To 2
Range(Selection, Selection.End(xlUp)).FillDown
ActiveCell.Offset(0, 1).Select
Next i
Line1:
Range("A:N").Select
Selection.Columns.AutoFit
Selection.ClearFormats

Range("C:c").Select
Selection.NumberFormat = "dd / mm / yyyy"
[a1].Select
ws1.Activate
[C1].Select
End Sub

• 169 views
• Top Contributor Asked on May 10, 2018 in

Thanks for the help. I have resolved the above isued. by giving following

“Scrap items – “& VLOOKUP(B11,INDIRECT(SELECT&”!B:C”),2,0)

I used indirect function to select different warehouse accordinly

Regards

Krishnan

• 169 views
• Top Contributor Asked on May 9, 2018 in

• 169 views
• Top Contributor Asked on May 9, 2018 in

Thanks for the reply. I am able to copy the same.

I request another help to complete the process as follows

There are two or three warehouses in different sheet like sheet1,ZYB1S,ZYF11AU etc..

On each line i have made warehouse a datavalisation to select the warehouse name, abd in column be the particular warehouse is opened for listing.  In column third (C11) i have used vlookup function to show the description of the item code on B11 from same warehouse which was opened.

What is the syntax to be written in vlookup to select the sheet i have selected in A11

Now i have written      =”Scrap items – “& VLOOKUP(B11,Sheet1!B:C,2,0)

The Sheet1 to be changed according to warehouse I select in A11.

Regards

Krishnan

• 169 views
• Top Contributor Asked on April 6, 2018 in

Dear Sir

I tried the same as follows

rng.Copy ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2).PasteSpecialxlPasteValues

Run Time error is coming : Object doesnot support this property or method

You can see on  item description column shown above  (=Scrap items ….)

I have forwarded the excel sheet with macro to info.analysistabs@gmail.com

Regards

Krishnan

• 169 views
• Top Contributor Asked on April 4, 2018 in

As informed I have tried the following

Change: rng.Copy Destination:=ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2)   To: rng.Copy ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2).PasteSpecial xlPasteValues

system is showing run time error

Unable to get the paste special property of the range class !!

Regards

Krishnan

• 169 views
• Top Contributor Asked on March 31, 2018 in

Please look into it, vlookup functions values are not copying to new sheet., Is any paste option or take only values

• 169 views
• Top Contributor Asked on March 22, 2018 in
 Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum =series+1 1 30161803SD00176 =”Scrap items – “& VLOOKUP(D2,Sheet1!D:E,2,0) =VLOOKUP(D2,Sheet1!D:F,3,0) =VLOOKUP(D2,Sheet1!D:G,4,0) Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum =series+1 2 30161803SD00182 =”Scrap items – “& VLOOKUP(D3,Sheet1!D:E,2,0) 1 =VLOOKUP(D3,Sheet1!D:G,4,0) Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum =series+1 3 30161803SD00206 =”Scrap items – “& VLOOKUP(D4,Sheet1!D:E,2,0) =VLOOKUP(D4,Sheet1!D:F,3,0) =VLOOKUP(D4,Sheet1!D:G,4,0) Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum =series+1 4 56101508SD04080 =”Scrap items – “& VLOOKUP(D5,Sheet1!D:E,2,0) =VLOOKUP(D5,Sheet1!D:F,3,0) =VLOOKUP(D5,Sheet1!D:G,4,0) Godrej & Boyce Mfg co Ltd, Malankara building, Palayam Trivandrum =series+1 5 30161803SD00250 =”Scrap items – “& VLOOKUP(D6,Sheet1!D:E,2,0) 1 =VLOOKUP(D6,Sheet1!D:G,4,0)
• 169 views
• Top Contributor Asked on March 22, 2018 in

• 169 views
• Top Contributor Asked on March 22, 2018 in

I have managed to get the details. but the values are not getting pasted due to v lookup function on the cells

Please correct the lines for filling sheet2 with values and not copying cells with formula

Sub Macro2()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim oc As Range
Dim ocd As Range
Dim WH As Range
Dim x As Range
Dim rw As Long
Dim rng As Range
Dim fin As Range
Dim i As Integer
Set ws1 = Worksheets(“zyfb1s bill”)
Set ws2 = Worksheets(“Sheet2”)
Set oc = ws1.[invbno]
Set ocd = ws1.[ocdt]
Set WH = ws1.[B9]
Set fin = ws1.[d19]
Set x = ws1.Range(“k:k”)
rw = Application.WorksheetFunction.CountA(x) + 4
Set rng = ws1.Range(“a11:k” & rw)
Application.ScreenUpdating = False
ws2.Activate
oc.Copy Destination:=ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1)
ocd.Copy Destination:=ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1)
WH.Copy Destination:=ws2.Cells(Rows.Count, 4).End(xlUp).Offset(1)
rng.Copy Destination:=ws2.Cells(Rows.Count, 5).End(xlUp).Offset(1, -2)
Cells(Rows.Count, 1).End(xlUp).Select
If ActiveCell.Offset(0, 11) = “” Then GoTo Line1
ws2.Cells(Rows.Count, 5).End(xlUp).Offset(0, -4).Select
For i = 1 To 2
Range(Selection, Selection.End(xlUp)).FillDown
ActiveCell.Offset(0, 1).Select
Next i
Line1:
ws1.Activate
‘WH.ClearContents
‘rng.ClearContents
[C1].Select
End Sub

• 169 views