Krishnan's Profile
Top Contributor
926
Points

Questions
1

Answers
18

    • 169 views
    • 24 answers
    • 0 votes
    • 169 views
    • 24 answers
    • 0 votes
  • Top Contributor Asked on May 17, 2018 in VBA: Macros.

    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

    only heading is filled.

    Sub Macro2()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim oc As Range
    Dim ocd As Range
    Dim adr 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 adr = ws1.[adr]
    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
    adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
    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
    • 24 answers
    • 0 votes
  • Top Contributor Asked on May 10, 2018 in VBA: Macros.

    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
    • 24 answers
    • 0 votes
    • 169 views
    • 24 answers
    • 0 votes
  • Top Contributor Asked on May 9, 2018 in VBA: Macros.

    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
    • 24 answers
    • 0 votes
  • Top Contributor Asked on April 6, 2018 in VBA: Macros.

    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

     

    RE: I want to copy selected cells and rows to another sheet in a row  with VBAYou can see on  item description column shown above  (=Scrap items ….)

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

    Please help

    Regards

    Krishnan

     

    • 169 views
    • 24 answers
    • 0 votes
  • Top Contributor Asked on April 4, 2018 in VBA: Macros.

    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 !!

     

    Please advise

     

    Regards

    Krishnan

    • 169 views
    • 24 answers
    • 0 votes
  • Top Contributor Asked on March 31, 2018 in VBA: Macros.

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

    • 169 views
    • 24 answers
    • 0 votes
  • Top Contributor Asked on March 22, 2018 in VBA: Macros.
    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
    • 24 answers
    • 0 votes
    • 169 views
    • 24 answers
    • 0 votes
  • Top Contributor Asked on March 22, 2018 in VBA: Macros.

    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 adr 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 adr = ws1.[adr]
    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
    adr.Copy Destination:=ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
    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
    ‘adr.ClearContents
    ‘WH.ClearContents
    ‘rng.ClearContents
    [C1].Select
    End Sub

    RE: I want to copy selected cells and rows to another sheet in a row  with VBA

    • 169 views
    • 24 answers
    • 0 votes