3 very simple macros needed

[Kim B]

Hi all. I know similiar requests have been made. I have tried to adapt the results to what I am trying to do and have had no luck so here goes.

I need a macro that will:

1. autofit all cells in 38 worksheets
2. copy the previous row without looking up or deleting or inserting. Just a simple copy previous row of values and formulas down one (to the next row empty or not)
3. Hide columns with headings hide in the first cell of the column.

Remember, I have 38 worsheets in the same workbook that it has to cycle thru. 4 of those are pivots, so no need to include those.

Thanks in advance.

 

[Smitty]

OK, #1 is fairly straightforward:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            ws.Cells.EntireColumn.AutoFit<br>        <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

You may be able to follow that methodology for the rest, but I'm confused on #2 & 3.

#2 - How does the code know what the "previous" row is/was? It seems like it won't be the last row as you want to copy down regardless of data being in the "next row".

#3 - How does the code know what's supposed to qualify as a heading?

Note that if I don't respond it's not because I'm being rude: I'm going surfing.

 

[Kim B]

Hope you had good surf!!

With respect to #2, the workbook has worksheets that have formulas and values from 2005 thru 2009. Prior to the month not being closed, the values are projections. Once the the month turns a new month, the pivot table will update the projected values with the linked formula from the row preceding it. The remaining rows beneath, will be projected values until I copy the links from the row before it.

Example:

Columns A B C D
Jan 2005 Formula Formula linked value linked value
Jan 2006 Formula Formula linked value linked value
Jan 2007 Formula Formula linked value linked value
Jan 2008 Formula Formula linked value linked value
May 2008 Formula Formula input input
Need to update May with linked value for columns c and d and maintain a and b formulas


With respect to # 3. The first cell in each of the columns on all of the sheets I want hidden have the word hide in row 1 of the columns. So if I want A G AA hidden, then cell A1, G1 and AA1 all have the word hide in them. Those columns are also grey shaded with 25% but I thought that was risky because if the the shading is not exactly the same, the macro won't work. The only reason I need a macro to hide these columns is because the macro to auto fit, will automatically unhide them unless you can think of a way to autofit and copy down and not unhide automatically.

I just want to make sure I was clear on something. I am assuming active.workbook will cycle thru all the sheets in the workbook? But what about the 4 sheets that have the pivots. I don't need these three macros applying to them. Especially the copy down one. That would really mess my linked sheets up.


Thanks

 

[Kim B]

Forgot one thing. The row below the May 2009 will have June 2009 projected inputs in it. That row needs to be left alone and just the May 2009 updated with the pivot links.

 

[Smitty]

Could you post some sample data? Check the Excel Forum main page and you'll see a post that explains how to post a shot(s) of your sheet.

And is your example referring to your data or the Pivot Table(s)? I also don't see how you're jumping from Jan 2008 to May 2008.

As for the hidden part (and the excluding the PT sheets) you can do that by amending my earlier example like this (note this is an example and not necessarily the most efficient way to do things):

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Sheet2" <SPAN style="color:#00007F">Or</SPAN> ws.Name <> "sheet3" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A1], Cells(1, Columns.Count).End(xlToLeft))<br>                    <SPAN style="color:#00007F">If</SPAN> LCase(c.Text) = "hide" <SPAN style="color:#00007F">Then</SPAN> c.EntireColumn.Hidden = <SPAN style="color:#00007F">True</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN> c<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Kim B]

<TABLE style="WIDTH: 370pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=492 border=0 x:str><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=123 height=17 x:num>200801</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 63pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=84></TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78 x:num="7670.666666666667"> 7,671 </TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 38pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff" align=right width=51 x:num="0.84043779434204591">0.840</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78 x:num="6379.833333333333"> 6,380 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>200802</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="7531.8333333333321"> 7,532 </TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff" align=right x:num="0.8246866818916061">0.825</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="6169"> 6,169 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>200803</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="7965.75"> 7,966 </TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff" align=right x:num="0.8293916580592261">0.829</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="6490.75"> 6,491 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>200804</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="7806.333333333333"> 7,806 </TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff" align=right x:num="0.85699025237014292">0.857</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="6418"> 6,418 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>200805</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="14236.246279816329"> 14,236 </TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff" align=right x:num="0.84400000000000008">0.844</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="6589.1783333333342"> 6,589 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>200806</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="8459.4981718603103"> 8,459 </TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffff" align=right x:num="0.84400000000000008">0.844</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="6604.2296666666671"> 6,604 </TD></TR></TBODY></TABLE>


Sorry, having trouble with the html conversion. Here is a little better setup. Right now 200804 (April 08) is an linked to a pivot table. 200805 and 200806 (May 08) and (June 08) are an input field. They will stay projected until I copy the previous row. I will need to copy exactly the formulas in the 200804 row down to the 200805 row so the inputs become actual data wich has been updated by refreshing the pivot table. 200806 will stay an input until June is closed and then I will copy 200805 down to 200806. This sheet continues with monthly projected data thru 2009. If it makes it easier. I think I might be able to referrence the row nbr that I am looking to copy from and the row nbr I am looking to paste to? So if that is the case, then the row I need to copy is row 72 and the row to paste into is 73.

 

[Smitty]

This may be a start:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">With</SPAN> ActiveCell.EntireRow<br>        .Copy ActiveCell.Offset(1)<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Wherever you place the activecell, the code will copy the entire row down one, then value the active cell's row.

You can replace the active cell reference with row 72 if you want to.

 

[Kim B]

So if replaced the activecell with 72, how would the code read?

 

[Kim B]

Just thought of something. After 72 is copied, it can't be valued across. There are formulas within that row as well as values. So I just need a simple copy and paste down 1 row command.

 

[Smitty]

So if replaced the activecell with 72, how would the code read?

This also removes the value part:

Rows(72).Copy Rows(72).Offset(1)