+ Reply to Thread
Results 1 to 3 of 3

Looping thru rows and copy cell values from "Inputs" sheet to "Output" sheet

  1. #1
    Registered User
    Join Date
    11-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    38

    Looping thru rows and copy cell values from "Inputs" sheet to "Output" sheet

    Dear All,

    On the below request i've been struggling for so long, I need your expertise skills to get this sorted out.

    Now here i wanted it Looping thru rows and copy cell values from "Inputs" sheet to "Outputsheet"

    Here is the code that i had written to copy OUTPUT(SHEET NAME) sheets(inputs).column BO(the number of rows that has value will vary and hence i created a loop that will run the macro until the active cell is blank). My macro starts from Range(BO2) and stretches all the way down column BO, it stops only when it hits a blank row
    Desired result for the macro will be to start copying the cell value in sheet(input).Range(BO2) paste it to sheet(Output).Range(A2:A10).

    For example, if "Peter" was the value in cell sheet(input),range(BN2) then when the marco runs and paste the value into sheet(Output) range(A2:A10). ie range A2:A10 will reflect "Peter"
    Then the macros loop back to sheet(inputs) & copy the next cell value and paste it to range(A11:A19) Example: "Dave" was the value in sheet(inputs) Range(BO3), then "Dave" will be paste into the next 9 rows in sheet(mywork).Range(A11:A19). A11:A19 will reflect "Dave"
    Again repeating the same process goes back to sheet(input) this time range(BN4), copys the value goes to sheet(Output) and paste it into A20:A29.
    Basically the process repeats

    Sub Button10_Click() Dim rngMyCell As Range
    Dim intMyLoopCount As Integer
    Dim wsInputTab As Worksheet
    Dim wsOutputTab As Worksheet

    Application.ScreenUpdating = False

    Set wsInputTab = Sheets("Inputs") 'Name of inputs sheet. Change to suit if necessary.
    Set wsOutputTab = Sheets("Output") 'Name of output sheet. Change to suit if necessary.

    For Each rngMyCell In wsInputTab.Range("BO2:BO" & wsInputTab.Cells(Rows.Count, "BO").End(xlUp).Row)
    If Len(rngMyCell) > 0 Then
    intMyLoopCount = 1
    Do Until intMyLoopCount > 9
    wsOutputTab.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = rngMyCell
    intMyLoopCount = intMyLoopCount + 1
    Loop
    End If
    Next rngMyCell

    Set wsInputTab = Nothing
    Set wsOutputTab = Nothing

    Application.ScreenUpdating = True

    MsgBox "Done."
    End Sub




    Since we have created 9 line items(OUTPUT- A2:A10) on each row of Inputs sheet(BO2),now on default these rows to be populated based heading names: First 9 rows of each line item of "Inputs sheet"(BO2)

    No
    Section
    (Outputs sheet heading- Col “Q”
    Item Code
    (Outputs heading- Col “R” Quantity Unit
    (Col “ V” Price Unit Quantity (Col –“U” Item TotalCol (AB)
    1 Energy Peak kwh $/kwh It should be fetched fromthe Column "Z" from the Inputs sheet(i.e Z2) It should be fetched fromthe Column "Z" from the Inputs sheet(i.e AA2)
    2 Energy Shoulder kwh $/kwh =Inputs!AD2 =Inputs!AE2
    3 Energy OffPeak kwh $/kwh =Inputs!AB2 =Inputs!AC2
    4 Network Capacity kwh $/kva/pa =Inputs!AN2 =Inputs!BH2
    5 Energy Service unit $ Default : 1 =Inputs!AL2
    6 Discount Discount unit $ Default : 1 =Inputs!AK2
    7 Total Total unit $ Default : 1 =SUM(AA2:AA7)
    8 TotalIncGst TotalIncGst unit $ Default : 1 =Inputs!AY2
    9 TotalDue TotalDue unit $ Default : 1 =Inputs!AZ2



    Sheet Heading name = Formula calculation :
    Issue date = Period TO date + 1 Day ( H2 = Inputs!P2+1)
    Due date = Period To Date + 30 Days (I2 =Inputs!P2 +30)
    Next Read Date = Period to date + 30 Days (O2= Inputs!P2+30)
    Price = From out sheet only Item Total /Quaintly (W2= AA2/U2)


    -> Based on Column heading these rows should be updated as below text
    IF the Column Heading is Commodity = “Electricity
    IF the Column Heading is Is Consolidated = False “
    IF the Column Heading is Is Bundled = “TRUE”
    IF the Column Heading is Is Reversal = “ False “
    IF the Column Heading is Is Final Bill = “False
    IF the Column heading is Band = “1”
    If the column heading is Losses = “1”
    If the column heading is Dollar Conversion “ 1”
    If the column heading is “Period Pro Rate = “ 1”
    If the column heading Is Actual Read = it should fetch from the Inputs sheet(IS Actual Read ) heading , If it appears “Estimate” than it should be updated with “ FALSE “ OR If it appears “ ACTUAL “ than it should be updated with “ TRUE”
    Capture.PNG
    Basically the process repeats....

    Can someone please help me out on this and get this sorted out and also i have attached excel sheet for your reference, your earliest support/response would be really grateful . Many thanks in advance

    Looking forward for your response.
    Last edited by hariexcel1987; 10-02-2015 at 01:52 PM.

  2. #2
    Registered User
    Join Date
    11-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    38

    Question Re: Looping thru rows and copy cell values from "Inputs" sheet to "Output" sheet

    Herewith enclosed working file for your reference.
    Attached Images Attached Images
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-17-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Looping thru rows and copy cell values from "Inputs" sheet to "Output" sheet

    Any update on this request ?

+ 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. "Worksheet change" not changing cell on other sheet to match target cell "fill".
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-02-2015, 12:02 PM
  2. Replies: 3
    Last Post: 06-05-2015, 01:55 PM
  3. Script not copying data from "Emails" sheet to "New Sheet" - Run time error: Object
    By methuselah90 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2014, 03:22 PM
  4. Replies: 3
    Last Post: 05-31-2013, 05:16 AM
  5. Enter Data on "Main" or "Input" Sheet and Copy to One of Many Other Sheets
    By timothy_no7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2012, 07:29 PM
  6. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  7. Replies: 3
    Last Post: 10-25-2005, 07:05 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