CODEWITHSUNDEEP
vbaexcel
Jon
Jon

Reputation: 433

Trying to set borders on range with same rows but different columns

I have basically a set of summary sheets that pull information together from a hundred or so worksheets - one summary is for information for the current cycle and the ones I'm messing with now break down current cycle information to individual department fiscal year tracking sheets. I've got the code working just fine for moving the information over, but I cannot get the right syntax for setting/selecting the correct range after the info is copied over to set borders around the newly added information. My code for moving the info is:

Sub FYFTEE()

    Dim shtCurrent As Worksheet
    Dim shtFY As Worksheet
    Dim LastCol As Long
    Dim CopyRng As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set shtCurrent = ThisWorkbook.Sheets("Cycle Summary")
    Set shtFY = ThisWorkbook.Sheets("FY12 D1")
    Set CopyRng = shtCurrent.Range("$C$12:$C$47")

    LastCol = shtFY.Cells("5", Columns.Count).End(xlToLeft).Column + 1

    With CopyRng
        shtFY.Cells("5", LastCol).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

    shtFY.Cells("4", LastCol).Value = shtCurrent.Range("I3").Value

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Thank you for your advice and direction!

Upvotes: 0

Views: 299

Answers (1)

Drew Gaynor
Drew Gaynor

Reputation: 8472

Assuming that you want a new set of borders around each set of copied data, declare this:

Dim destinationRange As Range

And change your With CopyRng statement to:

With CopyRng
    Set destinationRange = shtFY.Cells("5", LastCol).Resize(.Rows.Count, .Columns.Count)
    destinationRange.Value = .Value
    destinationRange.BorderAround Weight:=xlThick
End With

That will give you a thick border around each set of data copied over.

Or, if you need a border around everything on the destination sheet, you can use this instead of the code above:

shtFY.Cells.Borders.LineStyle = xlNone
shtFY.UsedRange.BorderAround Weight:=xlThick

Right after the line:

shtFY.Cells("4", LastCol).Value = shtCurrent.Range("I3").Value

Upvotes: 2

Related Questions