Macros - adding row and hide

[taken]

Been searching around for a while now without any answer. I'm creating an automatic schedule that calculates working hours and so forth for my employees. Instead of creating new rows at the bottom of the table manually, putting in all the funktions and style the cells with specific formats I want a macro to do this.

I have created a macro but, the problem is that everytime it is executed, it starts of course from the place where I created it in the first place.

So, the macro should somehow search for the last place in the table. Copy the two last and create new ones including the if-statements, cell format and styling.

The second macro I need to create is a toggle function where the macro searches for the rows that containts the working hour (underneath the times) and hides or unhides the rows. Is that possible?

I really hope that someone can help me! Thanks! /Martin

Screenshot: http://www.martinhult.se/screenshot.jpg
File: http://www.martinhult.se/schema_mall2013_macro.xlsm

thanks!

// martin

[arlu1201]

It will be good if you post your first code here so i can help you tweak it. \

Is this request -

The second macro I need to create is a toggle function where the macro searches for the rows that contains the working hour (underneath the times) and hides or unhides the rows. Is that possible?

related to the first one? If not, you can create a separate thread for it.

[taken]

As matter of face the code is in the file added to the url above.
I haven't done anything more then starting a recording when making the macro.
In this case it always starts from the same place when adding a new row, which becomes totally wrong...

The other macro for hiding and unhiding will be seperate. So. I'll create a new thred for that.

Thanks for a quick respons!

/ Martin

[arlu1201]

Can you please list down what actions you need to be performed? Like which rows / columns to be formatted which way?

Sometimes the recorded code just says "Selection.Font" and it does not show which rows / cells have been selected.

It will be nice if you have a list of formatting actions and functions which you need inserted so we can help you automate it.

[taken]

screenshot.jpg

Okay, I thought it just was to make something that makes a copy from the previous rows.

Do you need all the calculations for %, hour calculation and my total hours to the right to or will these be copied in the macro?
Don't really understand how things works here. When I try to copy rows manually everything works fine with the functions and calculations. I don't have to re-enter the functions so to speak.
But, the "add row"-button would be SO nice!
The same thing should happen for both weeks. So, the both tables should have one copy each so to speak.

thank you so much!

/ Martin

[arlu1201]

Oh yeah...we can do pastespecial for each action we need. Will work on this and get back to you.

[taken]

You're really helpfull. thank you!! =)

// martin

[arlu1201]

This code will help you copy the formats n formulae -

Option Explicit

Sub add_rows()
Dim lrow As Long

With Worksheets("Schema")
    lrow = .Range("C" & .Rows.Count).End(xlUp).Row
    .Range("C" & lrow - 27 & ":Q" & lrow).Copy
    .Range("C" & lrow + 5).PasteSpecial (xlPasteFormulasAndNumberFormats)
    .Range("C" & lrow + 5).PasteSpecial (xlPasteFormats)
End With
End Sub

Do you want any fields to be cleared once the copy paste is done?

[taken]

Where am I suppose to add that code to?

[arlu1201]

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button