| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert row on multiple sheets
If someone can help me please?
At the moment I am using 'Sheets("Mine").Activate' to insert a row on a specified sheet, but now I would like to insert the row using the code below on multiple sheets e.g (1st to last sheet) without having to name each sheet. Can this be done? Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "Total" Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin For i = n + 1 To 32 If Application.CountA(Rows(i)) = 0 Then Rows(i).Interior.ColorIndex = 2 End If Next i Range("A5").Select Rows("5:32").Select Selection.RowHeight = 12.75 Range("A5").Select End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert row on multiple sheets
Maybe...
Option Explicit Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim iRow As Long For Each wks In ActiveWorkbook.Worksheets With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wks End Sub Those dots in front of the .cells(), .rows(), .Font, ... are very important. They indicate that this property/method belongs to the object in the previous With statement. (Either the looping worksheet or the union.) aussiegirlone wrote: If someone can help me please? At the moment I am using 'Sheets("Mine").Activate' to insert a row on a specified sheet, but now I would like to insert the row using the code below on multiple sheets e.g (1st to last sheet) without having to name each sheet. Can this be done? Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "Total" Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin For i = n + 1 To 32 If Application.CountA(Rows(i)) = 0 Then Rows(i).Interior.ColorIndex = 2 End If Next i Range("A5").Select Rows("5:32").Select Selection.RowHeight = 12.75 Range("A5").Select End Sub -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert row on multiple sheets
Can you make it skip the first 5 sheets and start on the 6th
"Dave Peterson" wrote: Maybe... Option Explicit Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim iRow As Long For Each wks In ActiveWorkbook.Worksheets With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wks End Sub Those dots in front of the .cells(), .rows(), .Font, ... are very important. They indicate that this property/method belongs to the object in the previous With statement. (Either the looping worksheet or the union.) aussiegirlone wrote: If someone can help me please? At the moment I am using 'Sheets("Mine").Activate' to insert a row on a specified sheet, but now I would like to insert the row using the code below on multiple sheets e.g (1st to last sheet) without having to name each sheet. Can this be done? Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "Total" Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin For i = n + 1 To 32 If Application.CountA(Rows(i)) = 0 Then Rows(i).Interior.ColorIndex = 2 End If Next i Range("A5").Select Rows("5:32").Select Selection.RowHeight = 12.75 Range("A5").Select End Sub -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert row on multiple sheets
Dave the code works well , but is it possible to insert the row starting at
the 6th sheet instead of the 1st sheet? Please "Dave Peterson" wrote: Maybe... Option Explicit Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim iRow As Long For Each wks In ActiveWorkbook.Worksheets With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wks End Sub Those dots in front of the .cells(), .rows(), .Font, ... are very important. They indicate that this property/method belongs to the object in the previous With statement. (Either the looping worksheet or the union.) aussiegirlone wrote: If someone can help me please? At the moment I am using 'Sheets("Mine").Activate' to insert a row on a specified sheet, but now I would like to insert the row using the code below on multiple sheets e.g (1st to last sheet) without having to name each sheet. Can this be done? Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "Total" Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin For i = n + 1 To 32 If Application.CountA(Rows(i)) = 0 Then Rows(i).Interior.ColorIndex = 2 End If Next i Range("A5").Select Rows("5:32").Select Selection.RowHeight = 12.75 Range("A5").Select End Sub -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert row on multiple sheets
Option Explicit
Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim wCtr As Long Dim iRow As Long For wCtr = 6 To Worksheets.Count Set wks = Worksheets(wCtr) With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wCtr End Sub aussiegirlone wrote: Can you make it skip the first 5 sheets and start on the 6th "Dave Peterson" wrote: Maybe... Option Explicit Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim iRow As Long For Each wks In ActiveWorkbook.Worksheets With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wks End Sub Those dots in front of the .cells(), .rows(), .Font, ... are very important. They indicate that this property/method belongs to the object in the previous With statement. (Either the looping worksheet or the union.) aussiegirlone wrote: If someone can help me please? At the moment I am using 'Sheets("Mine").Activate' to insert a row on a specified sheet, but now I would like to insert the row using the code below on multiple sheets e.g (1st to last sheet) without having to name each sheet. Can this be done? Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "Total" Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin For i = n + 1 To 32 If Application.CountA(Rows(i)) = 0 Then Rows(i).Interior.ColorIndex = 2 End If Next i Range("A5").Select Rows("5:32").Select Selection.RowHeight = 12.75 Range("A5").Select End Sub -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert row on multiple sheets
Good Morning Dave
I see this morning in my email you replied again to my need for help on this matter. I thought you were not going to respond anymore and therefore, I had re entered my question for help yesterday. Responding to your email this morning and Testing the second code you gave works very well. However, My question has since been resolved and I Thankyou very much and those that helped me. PS: I hope you have a good day Sincerely Aussiegirlone "Dave Peterson" wrote: Option Explicit Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim wCtr As Long Dim iRow As Long For wCtr = 6 To Worksheets.Count Set wks = Worksheets(wCtr) With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wCtr End Sub aussiegirlone wrote: Can you make it skip the first 5 sheets and start on the 6th "Dave Peterson" wrote: Maybe... Option Explicit Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim iRow As Long For Each wks In ActiveWorkbook.Worksheets With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wks End Sub Those dots in front of the .cells(), .rows(), .Font, ... are very important. They indicate that this property/method belongs to the object in the previous With statement. (Either the looping worksheet or the union.) aussiegirlone wrote: If someone can help me please? At the moment I am using 'Sheets("Mine").Activate' to insert a row on a specified sheet, but now I would like to insert the row using the code below on multiple sheets e.g (1st to last sheet) without having to name each sheet. Can this be done? Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "Total" Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin For i = n + 1 To 32 If Application.CountA(Rows(i)) = 0 Then Rows(i).Interior.ColorIndex = 2 End If Next i Range("A5").Select Rows("5:32").Select Selection.RowHeight = 12.75 Range("A5").Select End Sub -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert row on multiple sheets
If you're going to start a new thread that continues an existing discussion,
then it would have been nice for you to post a followup to the existing thread with a note saying that. Then I wouldn't have wasted my time with my response. aussiegirlone wrote: Good Morning Dave I see this morning in my email you replied again to my need for help on this matter. I thought you were not going to respond anymore and therefore, I had re entered my question for help yesterday. Responding to your email this morning and Testing the second code you gave works very well. However, My question has since been resolved and I Thankyou very much and those that helped me. PS: I hope you have a good day Sincerely Aussiegirlone "Dave Peterson" wrote: Option Explicit Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim wCtr As Long Dim iRow As Long For wCtr = 6 To Worksheets.Count Set wks = Worksheets(wCtr) With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wCtr End Sub aussiegirlone wrote: Can you make it skip the first 5 sheets and start on the 6th "Dave Peterson" wrote: Maybe... Option Explicit Sub NewRow() Dim EndRowA As Long Dim NextRowAF As Long Dim wks As Worksheet Dim iRow As Long For Each wks In ActiveWorkbook.Worksheets With wks EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1 .Cells(NextRowAF, "AC").Value = "Total" .Cells(NextRowAF, "AF").Formula _ = "=sum(AF5:AF" & NextRowAF - 1 & ")" With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC")) .Font.Bold = True .Font.ColorIndex = 2 .Interior.ColorIndex = 32 .Borders.LineStyle = xlContinuous .Borders.ColorIndex = 2 .Borders.Weight = xlThin End With For iRow = NextRowAF + 1 To 32 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Interior.ColorIndex = 2 End If Next iRow .Rows("5:32").RowHeight = 12.75 End With Next wks End Sub Those dots in front of the .cells(), .rows(), .Font, ... are very important. They indicate that this property/method belongs to the object in the previous With statement. (Either the looping worksheet or the union.) aussiegirlone wrote: If someone can help me please? At the moment I am using 'Sheets("Mine").Activate' to insert a row on a specified sheet, but now I would like to insert the row using the code below on multiple sheets e.g (1st to last sheet) without having to name each sheet. Can this be done? Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "Total" Cells(n, "AF").Formula = "=sum(AF5:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True Union(Cells(n, "AF"), Cells(n, "AC")).Font.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 32 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.ColorIndex = 2 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin For i = n + 1 To 32 If Application.CountA(Rows(i)) = 0 Then Rows(i).Interior.ColorIndex = 2 End If Next i Range("A5").Select Rows("5:32").Select Selection.RowHeight = 12.75 Range("A5").Select End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert Columns in multiple sheets | Excel Worksheet Functions | |||
| Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
| How do you insert rows into multiple sheets in a workbook? | Excel Discussion (Misc queries) | |||
| insert Rows with Formulas in Place on Multiple Sheets? | Excel Discussion (Misc queries) | |||
| Insert same picture in multiple sheets and only change once | Excel Discussion (Misc queries) | |||
Linear Mode
