Hello there
i am looking for a code where i can copy thesame row on multiple sheets and then insert that copy under the copy'd row
this is what i have so far
the problem here is that it alway's copy row 42
Option Explicit
i am looking for a code where i can copy thesame row on multiple sheets and then insert that copy under the copy'd row
this is what i have so far
the problem here is that it alway's copy row 42
Option Explicit
VBA Code:
Sub AddRows()
Const CopyRow As Long = 42
Dim xCount As Variant
Do
xCount = Application.InputBox("Aantal rijen", "VERKOPEN DIE HANDEL", , , , , , 1)
If TypeName(xCount) = "Boolean" Then
MsgBox "You canceled.", vbExclamation
Exit Sub
End If
If xCount < 1 Then
MsgBox "the entered number of rows is to small, please enter again", vbCritical, "testing"
Else
Exit Do
End If
Loop
Dim ash As Object: Set ash = activeSheet
Dim Wb As Workbook: Set Wb = ash.Parent
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim wsCount As Long
For Each ws In Wb.Worksheets
wsCount = wsCount + 1
With ws.Rows(CopyRow)
.Copy
.Offset(-1).Resize(xCount).Insert xlShiftDown, xlFormatFromLeftOrAbove
End With
Next ws
Dim MsgString As String
MsgString = "Worksheets processed: " & wsCount
If wsCount > 0 Then
Application.CutCopyMode = False
ash.Select
MsgString = MsgString & vbLf & "Rows inserted: " & xCount
End If
Application.ScreenUpdating = True
MsgBox MsgString, vbInformation
End Sub