Excel VBA codes about background fill

Question

I work with MIT Excel tables. I need to copy "cell fill" to "another fill" and I've done this by using this code:

Sub Macro1()
    Columns("A:A").Copy
    Columns("B:B").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B1").Select
    Application.CutCopyMode = False
End Sub

How can I modify it so that it works between two different sheets?

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

For that you have to avoid using .Select and start working with objects. You may want to see THIS

So your code can be written as

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet

    '~~> Change as Applicable
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")

    ws1.Columns("A:A").Copy
    ws2.Columns("B:B").PasteSpecial Paste:=xlPasteFormats, _
                                    Operation:=xlNone, _
                                    SkipBlanks:=False, _
                                    Transpose:=False
    Application.CutCopyMode = False
End Sub

EDIT

Further to the discussion in comments, if you want to use the Codenames, then use this

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet

    '~~> Change as Applicable
    Set ws1 = Sheet1
    Set ws2 = Sheet2

    ws1.Columns("A:A").Copy
    ws2.Columns("B:B").PasteSpecial Paste:=xlPasteFormats, _
                                    Operation:=xlNone, _
                                    SkipBlanks:=False, _
                                    Transpose:=False
    Application.CutCopyMode = False
End Sub

edited May 23 '17 at 12:31

Community

1
1

answered Sep 09 '14 at 09:08

Siddharth Rout

142,730
17
199
246

I know that i am not really educated in this, but when i write in the code it doesn't work for me, do i have to change something in it, except i changed only sheet numbers to 3 and 4. – Annija Sep 09 '14 at 09:26
Does Col A have any formatting in the relevant sheet? – Siddharth Rout Sep 09 '14 at 09:26
Yes, it has fill colour, which is the main thing i need, some text and other formating. And as i try to run it it says - Run-time error 9, Subscript out of range – Annija Sep 09 '14 at 09:28
Yes, it has fill colour, which is the main thing i need, some text and other formating. – Annija Sep 09 '14 at 09:29
Well then the code will work as expected :) Check Col B of the other worksheet. BTW are both the sheets in the same workbook? – Siddharth Rout Sep 09 '14 at 09:30
For what should i check in col B? – Annija Sep 09 '14 at 09:32
Then check the names of the worksheets. You have spelt the name wrong (It may have an extra space in the name?) – Siddharth Rout Sep 09 '14 at 09:32
Since this is only a sample Workbook, i have the base names. I am not messing with the real table. And as it seems it doesn' t like smthn. about Set ws1 = ThisWorkbook.Sheets("Sheet3") – Annija Sep 09 '14 at 09:36
Are you sure the name of the worksheet is "Sheet3"? If you want to use code names then change the line `Set ws1 = ThisWorkbook.Sheets("Sheet3")` to `Set ws1 = [Sheet3]` similarly `Set ws2 = [Sheet4]` – Siddharth Rout Sep 09 '14 at 09:44
@SiddharthRout Why `[Sheet3]` rather than just `Sheet3`? – Rory Sep 09 '14 at 09:47
@Rory: No particular reason :) Old habits die hard I guess :) – Siddharth Rout Sep 09 '14 at 09:49
Thanks a lot for your help. :) – Annija Sep 09 '14 at 09:51
@Annija: You can remove the brackets and it will still work :) – Siddharth Rout Sep 09 '14 at 09:52

Excel VBA codes about background fill

1 Answers1