Shifting ranges in Excel VBA 1

[westma]

Hi there,
I have a macro that basically moves a bunch of cell ranges around, prints the worksheets, and moves the cells back to their original locations. A sample:

Range("Q85:W107").Select
    Selection.Cut Destination:=Range("A225:G247")
Range("Y85:AE107").Select
    Selection.Cut Destination:=Range("A249:G271")
Range("AG85:AM107").Select
    Selection.Cut Destination:=Range("A280:G302")

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1, Collate _
        :=True

Range("A280:G302").Select
    Selection.Cut Destination:=Range("AG85:AM107")
    Range("A249:G271").Select
    Selection.Cut Destination:=Range("Y85:AE107")
    Range("A225:G247").Select
    Selection.Cut Destination:=Range("Q85:W107")

My problem is that the user may want to insert a row/rows, which would cause each of the ranges to be off by the number of rows inserted.

To solve this, I placed a formula on my sheet (cell I57) to calculate the number of rows added. In the code, I added:

MoveRows = Range("I57").Value

I need all of the ranges above to adjust based on the value of MoveRows, for instance:

Range("Q(85+MoveRows):W(107+MoveRows").Select
    Selection.Cut Destination:=Range("A(225+MoveRows):G(247+MoveRows)")

I am not as clever a programmer as I would like, thus, I can't figure out the syntax to do this, and can't find a previous post to assist. Can you help? Thanks.

 

[PHV]

Range("Q" & (85 + MoveRows) & ":W" & (107 + MoveRows)).Cut Destination:=Range("A" & (225 + MoveRows) & ":G" & (247 + MoveRows))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[westma]

Thanks for your help. That was just what I needed!!