Looping through rows and copy cell values to another worksheet

[bcomhari]

Facing some diffculty in achieving the desired result for my macro.

Intention: I have a list of data in sheets(inputs).column BN(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(BN2) and stretches all the way down column BN, 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(BN2) 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(BN3), 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....
The macro ends the when the activecell in sheet(input) column A is empty. Urgently seek the advice/assistance from the experts in the forumn. Thanks in advance.

Your earliest response would be appreciated and many Thanks

[bcomhari]

Can you someone kindly help me to get this sorted out.

[SamT]

See Also: Delete Rows And Rename Headers (http://www.vbaexpress.com/forum/showthread.php?53831-Delete-Rows-And-Rename-Headers)

According the to above referenced Thread, at this time Column "BN" Header has been replaced with Header "Total."

I strongly suggest that you complete the "Data Alignment" you started in the referenced Thread before transferring Data to the Output sheet.

[bcomhari]

Yes, now i have completed all the formatting alignment in Inputs sheet ,now can you please help me to copy this to `output sheet as mentioned above.

[Trebor76]

Hi bcomhari,

Here's one way to do it:


Option Explicit

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

Application.ScreenUpdating = False

Set wsInputTab = Sheets("Input") 'Name of input 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("BN2:BN" & wsInputTab.Cells(Rows.Count, "BN").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

Regards,

Robert

[bcomhari]

Hi Robert,

Below is the Error one

Formula ->
If Len(rngMyCell) > 0 Then
Can you please get this fixed

[Trebor76]

Hi bcomhari,

Assuming the error is Run-time error '13' Type mismatch (it would have really helped if you said what the error message was) it is because you have an error in a cell (i.e. like #DIV/0!) within column BN. If that is the case, the following will cater for that:


Option Explicit

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

Application.ScreenUpdating = False

Set wsInputTab = Sheets("Input") 'Name of input 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("BN2:BN" & wsInputTab.Cells(Rows.Count, "BN").End(xlUp).Row)
If IsError(rngMyCell) = False Then
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
End If
Next rngMyCell

Set wsInputTab = Nothing
Set wsOutputTab = Nothing

Application.ScreenUpdating = True

MsgBox "Done."

End Sub

Regards,

Robert

[bcomhari]

Thanks a ton for your kind revert on this request, its working fine now. However I would like to add one more point here to get this sorted out.

è In Inputs data sheet I have created a unique number towards to last column and
Which I would like to copy that to first row of the same sheet
è I had written a formula for to calculate few values but I am getting the formula as the results instead of Values?
Most important thing is that, In Output sheet post getting unique values in the first column, it should do index/Vlookup/Match formula based on heading names and TEMP ID(A) to capture the info from Inputs sheet
Since I have created 9 line items on each row of Inputs sheet, now based on
Section heading these 9 rows have to added and accordingly capture the data in Total

14467



S.No
Section( Outputs sheet heading- Col “Q”
Item Code (Outputs heading- Col “R”
Quantity Unit (Col “ V”
Price Unit
Quantity (Col –“U”
Item Total
Col (AB)


1
Energy
Peak
kwh
$/kwh
Item code + Temp ID # matches it should picked it from Inputs sheet
Col “A”+ Col “R”

Item code + Temp ID # matches it should picked it from Inputs sheet
Col “A”+ Col “R”



2
Energy
Shoulder
kwh
$/kwh


3
Energy
OffPeak
kwh
$/kwh


4
Network
Capacity
kwh
$/kva/pa


5
Energy
Service
unit
$


6
Discount
Discount
unit
$


7
Total
Total
unit
$


8
TotalIncGst
TotalIncGst
unit
$


9
TotalDue
TotalDue
unit
$



OUTPUT SHEET – How it should picked it from the inputs sheet
Issue date: Period TO date + 1 Day
Due date : Period To Date + 30 Days
Next Read Date : Period to date + 30 Days
Price : From out sheet only Item Total /Quaintly


-> 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 “
If it appears “ ACTUAL “ than it should be updated with “ TRUE”

Final = Convert Output file to .csv
I think I am troubling you more, but still if you could help me out that will be really fantastic.

I have also attached detailed working file that I have worked her, please help me to get this closed .Your earliest would really grateful.

Looking forward to hear from you.

Do let me know if i need to any points here.

[Trebor76]

Thanks a ton for your help, i have added few more queries on the project that i am working on, It would be grateful if you look into that and revert at your earliest possible. Many thanks and you have been really superb for all the help

Please do not PM members asking for help as it defeats the purpose of having a public forum. Thanks.

You will have to start a new thread for your new requirement as it seems a totally different request from your original post and to be honest, I have no idea what you're asking :dunno

All the best with it.

Robert

[bcomhari]

OK, Many thanks and you have been really superb for all the help. Keep rocking :)

I have created a new thread as you suggested, kindly have a look at it and help me pls.

Regards,
Hari Prasad B