Repeat VBA for multiple sheets

alexbat

New Member
Joined
Dec 12, 2013
Messages
32
Wrote this code to copy/paste data from a input sheet to a summary sheet

Code:
    Sheet3.Select
    Range("A77").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeFormulas, 7).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheet21.Select
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I want to repeat the action for several sheets, and I´m aware that instead of repeating this code several times and changing the input sheet names, I could have a code that does the same action for all sheets in a range or sheets where the name contains similar characters. Tried to solve it myself but I´m stuck. Could someone help me with this?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

alexbat

New Member
Joined
Dec 12, 2013
Messages
32
Tried this now, but it does not loop, but performs the VBA ONLY to the active sheet no matter if it´s name starts with "Inp*" or not...

Code:
Sub Test()
Dim Sh As Worksheet
    For Each Sh In ActiveWorkbook.Worksheets
    If Sh.Name Like "Inp*" Then
    Range("A4:X73").Select
    Selection.Copy
    Sheets("TotalFTEList").Select
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub Test()
Dim Sh As Worksheet
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Name Like "Inp*" Then
            With Sh
                .Range("A4:X73").Copy
                Sheets("TotalFTEList").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next Sh
End Sub
 

Lasa1

New Member
Joined
Mar 31, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I am trying to repeat the same actions in various sheets but when I execute the macros, only the ActiveSheet is updated.
What am I doing wrong?

Sub Mastermacro()
Dim Sh As Worksheet

For Each Sh In ActiveWorkbook.Worksheets

Call InsertText
Call FormatText
Call Todoengris

Next Sh

End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Lasa1

You really should start a new thread for this, and in that thread post the code for the 3 subs you are calling
 

Lasa1

New Member
Joined
Mar 31, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank You Norie. I do not understand , Do you mean that I need to write again my question in this forum as a nw question istead of following the question from alexbat?
I am very new to all of this. Thank you for clarification
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,917
Office Version
  1. 365
Platform
  1. Windows
Yes please, that's exactly what you need to do. Thanks
 

Lasa1

New Member
Joined
Mar 31, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Thank You. I found the answer in the meantime but I got it for next question
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

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
Top