Paste to all sheets except some with criteria [Solved/Closed]

Report
Posts
6
Registration date
Thursday December 8, 2011
Status
Member
Last seen
February 3, 2013
-
Posts
6
Registration date
Thursday December 8, 2011
Status
Member
Last seen
February 3, 2013
-
Hi,

How can I make a macro paste copied data into some sheets of a worksheet but not other sheets. For example, I have 10 sheets in my worksheet that data need to be pasted in them , however, I have a few sheets that I don't want the data to be pasted in them (they have static names).

Here's the related portion of code that I have right now. This code will paste the data to every sheet. I would like add a condition that will not paste the data if the sheet name is "Data to Copy", "Main" and "Summary".

For i = 1 To Sheets.Count
Sheets(i).Select
Range("H400").Select
ActiveSheet.Paste
Range("A1").Select
Next

Any help would be greatly appreciated.


2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
765
you need to put a IF statement or a select statement


    For i = 1 To Sheets.Count
        sheetName = Sheets(i).Name
        Select Case sheetName
            Case Is = "Sheet1", "Sheet2", "Skip me"
                ' no action
            Case Else
                Sheets(i).Select
                Range("H400").Select
                ActiveSheet.Paste
                Range("A1").Select
        End Select
    Next
Posts
6
Registration date
Thursday December 8, 2011
Status
Member
Last seen
February 3, 2013

Thank you so much, this worked. One observation is that the names within the select statement are case sensitive.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!