Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Answer 1 / 2
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
Answer 2 / 2
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.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!