PDA

View Full Version : Looping through rows and copy cell values to another worksheet



bcomhari
09-29-2015, 07:16 PM
Dear All,

I would request to kindly look into this issue and revert at your earliest convenience.

INPUTS(Sheet name)

Problem#1

Please refer Column " BN" in Inputs sheet whereas formulas are updated as Text values , below is the code that i have used :



Sheets("Inputs").Select

Range("A1").Select
Selection.End(xlToRight).Select
Range("BN1").Select
ActiveCell.FormulaR1C1 = "Total"
Range("BN2").Select


Range("BN2").Formula = "=Sum(AN2+AK2+AL2+AB2+AD2+z2)"


Range("BN2").Select
Selection.Copy
Range("BM1").Select
Selection.End(xlDown).Offset(-1, 1).Select

Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Problem # 2 : Now here i wanted entire "BO" column to be cut and pasted with values in " A" column in the same sheet





Range("BO1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("BO1").Select
Selection.End(xlToLeft).Select
Columns("A:A").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


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)

















S.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”

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.

Regards,
Hari Prasad B

p45cal
09-30-2015, 01:01 AM
Once again cross posting without providing links.

http://www.mrexcel.com/forum/excel-questions/890534-looping-through-rows-copy-cell-values-another-worksheet.html

bcomhari
09-30-2015, 07:46 AM
Looking forward to hear from your response on this request, can someone please help here at your earliest convenience

bcomhari
09-30-2015, 07:06 PM
Looks like No response yet ? Can someone please guide me here