Copy same row on multiple sheets and insert n times on those sheets

sivior

New Member
Joined
Apr 6, 2022
Messages
2
Platform
  1. Windows
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

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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,214,544
Messages
6,120,126
Members
448,947
Latest member
test111

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top