0

I have a script, a very simple script, which is part of a much larger script, and right now this last bit is giving me issues and I don't know why.

This is the part of the script that is giving me problems.

Option Explicit


Sub Test()

    Dim wsJL As Worksheet 'Jobs List
    Dim lastrow As Long    
    Set wsJL = Sheets("Jobs List")

    lastrow = wsJL.Range("B" & Rows.Count).End(xlUp).Row
    wsJL.Range("B3:N3").Copy
    wsJL.Range("B4:N" & lastrow).PasteSpecial xlPasteFormats

End Sub

Here is the workbook.

https://dl.dropbox.com/u/3327208/Excel/format.xlsm

As far as I can tell what I am doing should not be happening. What is happening is it overwrites everything, instead of replacing the format with what was originally copied.

Can someone please attempt to reproduce thisand tell me where my mistake is?

Thanks.

Community
  • 1
  • 1
Matt Ridge
  • 3,633
  • 16
  • 45
  • 63
  • This code looks okay. Are you sure this is the part that doesn't work? Can you try to run this exact code on your worksheet? (Sorry I can't download your file from my current machine to test...) – Rachel Hettinger Sep 19 '12 at 21:04
  • 3
    I just ran it and it looks good. Copied the format down until `lastrow`. Did not copy any of the data from line 3 over the other rows. I even changed the formatting a bit to test. Worked. – Scott Holtzman Sep 19 '12 at 21:39
  • I'll try it at work tomorrow, my computer sometimes acts funny at work. I don't know why but it does. Eh... thanks for the trials I thought it looked good, I just needed a verify. – Matt Ridge Sep 20 '12 at 00:22
  • Ok, just tried it again, figured out what was wrong, it was EBKAC. There was a cell further down that I didn't know was there. It's fixed now. – Matt Ridge Sep 20 '12 at 12:10

1 Answers1

2

You can do the same thing without copying and pasting. It requires a little more code, but doesn't use the clipboard and doesn't select anything on your sheet.

It runs dramatically faster. I'd be curious to know if this works better on your work computer than what you have already tried.

Try this:

Sub WriteInsteadOfCopyFormatting()

    Dim wsJL As Worksheet 'Jobs List
    Dim rng As Range ' Columns B through N
    Dim dateRng As Range ' Column F
    Dim numbersRng As Range ' Columns J through L
    Dim NCMRRng As Range ' Column M

    Dim lastRow As Long
    Dim firstCopyRow As Long

    Set wsJL = Sheets("Jobs List")

    lastRow = wsJL.Range("B" & Rows.Count).End(xlUp).Row
    firstCopyRow = 4
    Set rng = wsJL.Range("B" & firstCopyRow & ":N" & lastRow)
    Set dateRng = wsJL.Range("F" & firstCopyRow & ":F" & lastRow)
    Set numbersRng = wsJL.Range("J" & firstCopyRow & ":J" & lastRow)
    Set NCMRRng = wsJL.Range("M" & firstCopyRow & ":M" & lastRow)

    With rng
        .Interior.Color = 16777215
        .Font.Name = "Calibri"
        .Font.Size = 11
        .Font.Color = vbBlack
        .BorderAround Weight:=xlThin, Color:=vbBlack
    End With
    With rng.Borders(xlInsideVertical)
        .Weight = xlThin
        .Color = vbBlack
    End With
    With rng.Borders(xlInsideHorizontal)
        .Weight = xlThin
        .Color = vbBlack
    End With

    dateRng.NumberFormat = "d/mm/yyyy"
    numbersRng.NumberFormat = "#,##0"
    NCMRRng.NumberFormat = "0######"

End Sub
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
  • I tried it for S&Gs and it moves a bit faster, but not enough to warrant the extra code. – Matt Ridge Sep 20 '12 at 13:35
  • 1
    Just be aware that copying and pasting using the clipboard is the same as selecting, and selecting in code should (and can) almost always be avoided. Read this excellent thread for more details: http://stackoverflow.com/q/10714251/138938 – Jon Crowell Sep 20 '12 at 13:47
  • Thanks, I'll look into it. I am just doing this right now to get things up and running. – Matt Ridge Sep 20 '12 at 17:41