Find specific empty Row (excel macro) - Range("C" & Rows.Count).End(xlUp).Offset(1).Row

[faswad21]

Hello Guys,

I have seen similar threads on the forum but none has gotten a final solution.

I would really appreciate your help, My requirement is pretty simple.

so this code, Range("C" & Rows.Count).End(xlUp).Offset(1).Row -> Is supposed to give me the last available row in column C which it did in CellC39

But, Cell C38 to F38 holds data that is supposed to be the total sum of all the data above.

So my requirement is to get Macro to skip Cell C38 and copy the first data into Cell C7.

For each data output, I want the code to continue from C7, C8, C9, C10 up till Cell C37.

Current Problem is, I have tried all possible solutions but I cant find a fix.

kidly find below my complete code.


**Sheets Details**
Macro copies some data from the "Daily Expense" Sheet
and save into the "Month_Summary" Sheet


**My Code**

Sub dailydata_Expense()

' Allow macros to edit the worksheet, but not the user.
' - user can still remove the worksheet protection by hand if they have the password.

ActiveSheet.Protect Password:="@army21", UserInterfaceOnly:=True


ws_output = "Month_Summary"

next_row = Sheets(ws_output).Range("C" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 3).Value = Range("Date").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Cash_Spent").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("Pos_Spent").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("Total_Expense").Value


End Sub

 

[Fluff]

How about

With Sheets(ws_output)
   If .Range("C7") = "" Then
      next_row = 7
   Else
      next_row .Range("C6").End(xlDown).Row
   End If
End With

 

[faswad21]

Thank you for the prompt response Fluff,

it only saved the first data set.

but on the second entry, I get a runtime error.

pictures attached.

 

[Fluff]

Oops it's missing an = sign after next_row

 

[faswad21]

Oops it's missing an = sign after next_row

Good Progress @ Fluff.

But it keeps on overwriting the data in C7.

I need it to save the next data in the next empty row below cell C7.

thank you always.

 

[Fluff]

Oops, it should be

next_row =  .Range("C6").End(xlDown).Offset(1).Row

 

[faswad21]

wow wow wow.

this is so amazing fluff.

it really worked perfectly.

and even if I delete the data it starts from the correct position.

A good effort.


My final trouble now is.

How do I make the macro auto unprotect the sheets and save its data then protect the sheets back again to prevent user modifications to my locked cells?


I tried this code ---
' Allow macros to edit the worksheet, but not the user.
' - Users can still remove the worksheet protection by hand if they have the password.

ActiveSheet.Protect Password:="@army21", UserInterfaceOnly:=True


but the money I save some data and close the workbook.


the sheets become protected and thus I get errors when I run my macro codes that it cant copy to the protected sheet.


I hope my question is a bit clear.

Thanks again.

 

[Fluff]

Put this at the start of the macro

ActiveSheet.Unprotect Password:="@army21"

and this at the end

ActiveSheet.Protect Password:="@army21"

 

[faswad21]

Good

Put this at the start of the macro

ActiveSheet.Unprotect Password:="@army21"

and this at the end

ActiveSheet.Protect Password:="@army21"

PROBLEM SOLVED 100 %​

Day Sir Fluff.

I really appreciate your kind assistance.

Apologies for the late reply, It was already late on my side.


I tried your code and it worked but then because I have 3 sheets that I'm working with. I believe the other macro must have caused the error.

so basically what I resorted to was to specify the exact sheet I want the macro to protect and unprotect and it worked like magic.


**Here is the code that worked for me.**

Sub MyMacro ()

Sheet2.Unprotect Password:="Secret"

'MY CODE

Sheet2.Protect Password:="Secret"

End Sub


I repeated the same for the other macro selecting sheet 4.


Thank you so much for your prompt response. God Bless.

Do have a great week ahead.

 

[Fluff]

Glad you sorted it & thanks for the feedback.