Results 1 to 4 of 4

Thread: PasteFormat into Columns if Condition is Met

  1. #1
    Neophyte ricardo9316's Avatar
    Join Date
    Aug 2018
    Posts
    2
    Articles
    0
    Excel Version
    2011

    PasteFormat into Columns if Condition is Met



    Register for a FREE account, and/
    or Log in to avoid these ads!

    So guys, I am trying to tell VBA to Paste Format over every column if the condition is met within the Range (C1:N1) equalizing to 1. Problem is I keep getting this excel issue when trying to run.

    "you can't paste this here because the copy area and paste area aren't the same size"

    Is there another way to write the script to bypass this? Maybe instead applying against entire Columns we can try applying to ranges within the columns aligned with the cells that meet the condition.


    Sub Macro1()

    Columns("Z:Z").Copy

    For Each c In Range("C1:N1")

    If c.Value = 1 Then

    c.Columns.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If

    Next c
    End Sub

  2. #2
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    27
    Articles
    0
    Excel Version
    2016
    Hello Ricardo,

    I've just tested your code and it works just fine for me. If it still doesn't work for you, then try it amended a little as follows:-


    Code:
    Sub Macro1()
    
    Dim c As Range
    
    Application.ScreenUpdating = False
    
    For Each c In Range("C1:N1")
          Columns("Z:Z").Copy
          If c.Value = 1 Then
          c.Resize(, c.Columns).PasteSpecial Paste:=xlPasteFormats
          End If
    Next c
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    End Sub
    I hope that this helps.

    Cheerio,
    vcoolio.
    Last edited by vcoolio; 2018-08-17 at 05:57 AM. Reason: Code tags.

  3. #3
    Neophyte ricardo9316's Avatar
    Join Date
    Aug 2018
    Posts
    2
    Articles
    0
    Excel Version
    2011
    Hey man,

    Could there be something missing after your code where it reads ... c.Resize(????, c....

    I get an error

  4. #4
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    27
    Articles
    0
    Excel Version
    2016
    Hello Ricardo,

    No, there is nothing missing.

    What error are you receiving?

    I've again tested both codes and both work just fine on my end. Mind you, the sample I created is only what I believe your worksheet to look like so there could be something else at play in your actual worksheet.

    Upload a sample of your workbook so that we can see its set out and test with it. If your data is sensitive then please use dummy data.

    Cheerio,
    vcoolio.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •