VBA Macro to Insert Rows into All Visible Sheets

vaflyfisher · Jul 16th 2022

Hello, I hope someone can help me with my code. I have a workbook that splits data from a Main worksheet into multiple worksheets based on the data in column A. After that macro runs, I'm trying to take data from another sheet and copy/paste into all the sheets I just created that contains a "key" to the data in each worksheet. For some reason it copies the rows, but won't paste onto the other worksheets. I'm new at this, and am cobbling this together bit by bit.

Code

Sub Addrows()
    Dim mySheet As Object
    Sheet37.Activate
    Range("1:13").Copy
    For Each mySheet In Sheets
        With mySheet
            If .Visible = True Then .Select Replace:=False
            Range("A1").Select
            ActiveSheet.Paste
        End With
    Next mySheet
End Sub

Display More

vcoolio · Jul 17th 2022

Hello VA,

Are you looking at copy/pasting rows 1-13 from sheet37 (assume this is the sheet code) to all the other sheets starting at A1? If so, try the code amended as follows:-

Code

Sub TestCopy()


Dim ws As Worksheet, sh As Worksheet
Set sh = Sheet1


Application.ScreenUpdating = False


For Each ws In Worksheets
If ws.Name <> "Sheet37" Then
sh.Rows("1:13").Copy ws.[A1]
End If
Next ws


Application.ScreenUpdating = True


End Sub

Display More

You'll need to change Sheet37 to the actual name of your source sheet.

I hope that this helps.

Cheerio,

vcoolio.

vcoolio · Jul 17th 2022

Hello VA,

I've just realised that in the code above I've mistakenly set the sh variable to Sheet1. It should be 37.

Cheerio,

vcoolio.

vaflyfisher · Jul 17th 2022

Cheerio! Thanks, that did it!

vcoolio · Jul 18th 2022

You're welcome VA. I'm glad to have been able to assist.

Cheerio,

vcoolio.

VBA Macro to Insert Rows into All Visible Sheets

Participate now!

Share

Similar Threads

ProductSearch - Unhiding/Hiding Rows on Multiple Worksheets based on Column Value

Insert New Row if needed

VBA Macro to Transpose Rows to Columns for the Entire Sheet

Help with creating VBA to clean up and organize some data

Collecting data from multiple rows in one cell

VBA Code to copy value in a cell and paste in at the first row of the Table and delete rows below after