+ Reply to Thread
Results 1 to 18 of 18

paste the data of what the quantity is. I Wish anybody here can help me please :(

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Question paste the data of what the quantity is. I Wish anybody here can help me please :(

    Good Evening Excel Forum. I have a problem anybody can help me ?? Is it posible to put the data of what is the quantity in Cell "F" is. in sheet 1 is the data. in Sheet 2 is the Result what i want. I wish Anyone Here can help. GODBLESS Always This Forum. This Forum is really help to. Godbless Again. BLess This Forum it's Really help
    Attached Files Attached Files
    Last edited by jasond1992; 11-30-2015 at 09:12 PM.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Give this a try -

    Sub jasond1992() 'http://www.excelforum.com/excel-programming-vba-macros/1115257-paste-the-data-of-what-the-quantity-is-i-wish-anybody-here-can-help-me-please.html
        
        Application.ScreenUpdating = False
        
        Dim WS As Worksheet, LR As Integer, Counter As Integer, NewWS As Worksheet, LR2 As Integer, CopyCount As Integer, LC As Byte
        
        Set WS = Sheets("Sheet1")
        Set NewWS = Sheets.Add
        
        WS.Rows("1:1").Copy ActiveSheet.Range("A1")
        
        LR = WS.Cells(Rows.Count, 1).End(xlUp).Row
        LC = WS.Cells(1, Columns.Count).End(xlToLeft).Column
        
        For Counter = 2 To LR
            LR2 = NewWS.Cells(Rows.Count, 1).End(xlUp).Row + 1
            CopyCount = WS.Cells(Counter, LC).Value
            WS.Select
            WS.Range(Cells(Counter, 1), Cells(Counter, LC)).Copy NewWS.Cells(LR2, 1)
            NewWS.Select
            NewWS.Range(Cells(LR2, 1), Cells(LR2 + CopyCount, LC)).FillDown
        Next Counter
        
        NewWS.Cells.EntireColumn.AutoFit
        
        Application.ScreenUpdating = True
        
    End Sub
    See the file attached.
    Attached Files Attached Files
    Last edited by NeedForExcel; 11-27-2015 at 06:48 AM.
    Cheers!
    Deep Dave

  3. #3
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Hello sir NeedForExcel. is it possible the result is in Sheet 1 not in the other sheet sir ?? and is it possible to change the quantity into 1 when the result is paste sir ? i wish you can help me sit.. GODBLESS.

  4. #4
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    And sir the result is excess 1 not exactly what the quantity is sir. Sorry for the Grammar sir. I really appreciate your help sir NeepForExcel I wish you can help this will really help me so much. GODBLESS Again Sir.

  5. #5
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Quote Originally Posted by jasond1992 View Post
    And sir the result is excess 1 not exactly what the quantity is sir. Sorry for the Grammar sir. I really appreciate your help sir NeepForExcel I wish you can help this will really help me so much. GODBLESS Again Sir.
    I am not sure what that means.. Can you explain a bit more?

  6. #6
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Dont Remind The Cell "A" The Program will take affect in B to F only Sir. Did i explain well what i mean sir. i wish you understand sir, Thaksyou so much.

  7. #7
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    i dont know why the result become 11 sir. try to run the program and count the result sir.

  8. #8
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    I Try to run the file sir. the result for QUANTITY 10 is 11 data. Excess 1 data sir. like the other's when i run the program the data have a 1 excess data.


    Here's a sample pic what i want sir. the 1st is no program already.
    1.png

    And these is what i want if i run the program sir

    2.png

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    But why is it 11 data?

    My code generates the line number of times thats mentioned in the quantity column.. Is it not how you want it?

  10. #10
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Please see post#7

  11. #11
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    The result in my sheet is 10, not 11..

  12. #12
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Its okay now sir. can you do when run the program. the result is in 1 sheet only ? and the quantity 10 became 1 when program is run.

    the program will take affect in range B to F only not in A or G

  13. #13
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Try this code -

    Sub jasond1992() 'http://www.excelforum.com/excel-programming-vba-macros/1115257-paste-the-data-of-what-the-quantity-is-i-wish-anybody-here-can-help-me-please.html
        
        Application.ScreenUpdating = False
        
        Dim WS As Worksheet, LR As Integer, Counter As Integer, NewWS As Worksheet, LR2 As Integer, CopyCount As Integer, LC As Byte, SHName As String
        
        Set WS = Sheets("Sheet1")
        SHName = WS.Name
        Set NewWS = Sheets.Add
        
        WS.Rows("1:1").Copy ActiveSheet.Range("A1")
        
        LR = WS.Cells(Rows.Count, 1).End(xlUp).Row
        LC = WS.Cells(1, Columns.Count).End(xlToLeft).Column
        
        For Counter = 2 To LR
            LR2 = NewWS.Cells(Rows.Count, 1).End(xlUp).Row + 1
            CopyCount = WS.Cells(Counter, LC).Value
            WS.Select
            WS.Range(Cells(Counter, 1), Cells(Counter, LC)).Copy NewWS.Cells(LR2, 1)
            NewWS.Select
            NewWS.Range(Cells(LR2, 1), Cells(LR2 + CopyCount, LC)).FillDown
        Next Counter
        
        Range("A2").Value = 1
        Range("A3").Value = 2
        Range("A2:A3").AutoFill Destination:=Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        
        Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row).Value = 1
        
        Application.DisplayAlerts = False
        NewWS.Cells.EntireColumn.AutoFit
        Sheets(SHName).Delete
        NewWS.Name = SHName
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
    End Sub

  14. #14
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Sir what if i will change the range. my data Start in B5:F5 when i run it will paste the result in B5:F5 what code did i need to change sir ? and sir i create a shape to assign macro when i run it the shape is lost. ? Thanks you so much sir to this Program. Godblesss..

  15. #15
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    And where can i edit the range if i add 100 row of data sir. Thankyou so much sir.

  16. #16
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Hi,

    PFA Execl Sheet attached.. I'v considered Column B to be the 1st one always..

    Sub jasond1992Changed() 'http://www.excelforum.com/excel-programming-vba-macros/1115257-paste-the-data-of-what-the-quantity-is-i-wish-anybody-here-can-help-me-please.html
        
        Application.ScreenUpdating = False
        
        Dim WS As Worksheet, LR As Integer, Counter As Integer, NewWS As Worksheet, LR2 As Integer, CopyCount As Integer, LC As Byte, SHName As String, FC As Integer
        
        Set WS = ActiveSheet
        
        If WS.Range("B1") = "" Then FC = WS.Range("B1").End(xlDown).Row Else FC = 1
        
        SHName = WS.Name
        Set NewWS = Sheets.Add
        
        WS.Activate
        WS.Range(Cells(FC, 2), Cells(FC, 7)).Copy NewWS.Cells(FC, 2)
        NewWS.Activate
        
        LR = WS.Cells(Rows.Count, 2).End(xlUp).Row
        LC = WS.Cells(FC, Columns.Count).End(xlToLeft).Column
        
        For Counter = FC + 1 To LR
            LR2 = NewWS.Cells(Rows.Count, 2).End(xlUp).Row + 1
            CopyCount = WS.Cells(Counter, LC).Value
            WS.Select
            WS.Range(Cells(Counter, 2), Cells(Counter, LC)).Copy NewWS.Cells(LR2, 2)
            NewWS.Select
            NewWS.Range(Cells(LR2, 2), Cells(LR2 + CopyCount, LC)).FillDown
        Next Counter
        
        Cells(FC + 1, 2).Value = 1
        Cells(FC + 2, 2).Value = 2
        Range("B" & FC + 1 & ":B" & FC + 2).AutoFill Destination:=Range("B" & FC + 1 & ":B" & Cells(Rows.Count, 2).End(xlUp).Row)
        
        Range("G" & FC + 1 & ":G" & Cells(Rows.Count, 2).End(xlUp).Row).Value = 1
        
        Application.DisplayAlerts = False
        NewWS.Cells.EntireColumn.AutoFit
        ActiveSheet.Shapes.AddShape(msoShapeRectangle, 392.25, 118.5, 60, 27.75).Select
        Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "RUN"
        Selection.OnAction = "jasond1992Changed"
        Range("A1").Select
        Sheets(SHName).Delete
        NewWS.Name = SHName
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
    End Sub
    Please excuse my poor coding for this instance..
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    08-20-2015
    Location
    philippines
    MS-Off Ver
    2013
    Posts
    260

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Sir where in this code i will adjust ? i will add another column ?

    sample.png



    Sub Jasond1992()
        
        Application.ScreenUpdating = False
        
        Dim WS As Worksheet, LR As Integer, Counter As Integer, NewWS As Worksheet, LR2 As Integer, CopyCount As Integer, LC As Byte, SHName As String, FC As Integer
        
        Set WS = ActiveSheet
        
        If WS.Range("A1") = "" Then FC = WS.Range("A1").End(xlDown).Row Else FC = 1
        
        SHName = WS.Name
        Set NewWS = Sheets.Add
        
        WS.Rows(FC & ":" & FC).Copy ActiveSheet.Cells(FC, 1)
        
        LR = WS.Cells(Rows.Count, 1).End(xlUp).Row
        LC = WS.Cells(FC, Columns.Count).End(xlToLeft).Column
        
        For Counter = FC + 1 To LR
            LR2 = NewWS.Cells(Rows.Count, 1).End(xlUp).Row + 1
            CopyCount = WS.Cells(Counter, LC).Value
            WS.Select
            WS.Range(Cells(Counter, 1), Cells(Counter, LC)).Copy NewWS.Cells(LR2, 1)
            NewWS.Select
            NewWS.Range(Cells(LR2, 1), Cells(LR2 + CopyCount, LC)).FillDown
        Next Counter
        
        Cells(FC + 1, 1).Value = 1
        Cells(FC + 2, 1).Value = 2
        Range("A" & FC + 1 & ":A" & FC + 2).AutoFill Destination:=Range("A" & FC + 1 & ":A" & Cells(Rows.Count, 1).End(xlUp).Row)
        
        Range("F" & FC + 1 & ":F" & Cells(Rows.Count, 1).End(xlUp).Row).Value = 1
        
        Application.DisplayAlerts = False
        NewWS.Cells.EntireColumn.AutoFit
        NewWS.Cells.EntireRow.AutoFit
        Sheets(SHName).Delete
        NewWS.Name = SHName
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
    End Sub

  18. #18
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: paste the data of what the quantity is. I Wish anybody here can help me please :(

    Please attach your workbook with your actual data range.. Ill help you out with the code

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Running total of Quantity on Hand Vs Sales Vs Quantity to Produce
    By jespo1351 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2015, 07:34 AM
  2. Replies: 4
    Last Post: 01-14-2015, 07:46 AM
  3. Transferring data from one tab to another when quantity entered
    By Cuffy70 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2013, 06:15 PM
  4. Replies: 3
    Last Post: 05-02-2011, 02:44 PM
  5. Divide monthly Quantity into Weekly quantity ...
    By jitendra.mistry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2010, 01:36 PM
  6. Replies: 5
    Last Post: 11-23-2009, 06:24 PM
  7. Average that change with quantity of data
    By feejo in forum Excel General
    Replies: 5
    Last Post: 08-01-2007, 06:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1