Help with Copying Ranges and Offsets

[lfwake2wake]

I have a spreadsheet where I want to copy data from one sheet to another. The range to be copied is dependent on a few variables. It will always be 55 rows long and 'j' columns wide. Variable j represents the variable that will change every time this macro is run. I'm having issues when I try to activate the range to be copied. I get an application error or a with block error depending on whether the with block is int he code. I saw this on another website which had me insert the with block: http://stackoverflow.com/questions/1...d-error-in-vba
It's been a while since I've coded and I am very rusty. Anyways, here is my code below. I feel like if I can get past this copying the range part and understanding my errors using offset, I can easily finish up what I want it to do - which is why my code will appear to be useless (I haven't finished the rest of it yet). So thanks in advance!

Sub CopyStuff()

Dim noProp, noStreams, countInt As Integer
Dim pasteRng As Range

Worksheets("Summary").Activate

noProp = Range("B1")
noStreams = Range("B2")

With Worksheets("Output")

Range("E5").Activate

pasteRng = Range(ActiveCell, ActiveCell.Offset(55, noStreams - 1))

End With

End Sub

[Mayda89]

Hi,

in this line of code

pasteRng = Range(ActiveCell, ActiveCell.Offset(55, noStreams - 1))

you are trying to subtract 1 from a the range nostreams. Since nostreams is a range, not an integer, you get an error.

Next, from that line of code, I can't really understand which cell you want to refer to. Tell me, and I'll post the code you need.

[lfwake2wake]

Thanks for the response.

I see what you mean by the range. Confused me at first. How do I assign a value from a cell without defining it as a range? My intentions are to keep noStreams as an integer as declared.

I want to copy a range that is 55 rows down from the active cell and "noStreams" columns wide.

[Norie]

You aren't really using the with.

Give this a try, I've added dot qualifiers to tie the ranges to the worksheets in the Withs.

Sub CopyStuff()

Dim noProp As Long
Dim noStreams As Long
Dim countInt As Long
Dim pasteRng As Range

    With Worksheets("Summary")

        noProp = .Range("B1").Value
        noStreams = .Range("B2").Value
        
    End With

    With Worksheets("Output")

        pasteRng = .Range(.Range("E5"), .Range("E5").Offset(55, noStreams - 1))

    End With

End Sub

[lfwake2wake]

See the issue is I have a lot of data on the worksheet "Output". The format is atrocious and I am trying to build a macro to reformat it. I am building a tool to reformat data that is propagated from a piece of simulation software. On the Output worksheet, I have 8 pieces of data (in this particular project...it will be different next time). When the data is propagated, it fill in the Output worksheet starting at E5, going down 55 cells and is noStreams columns wide. Then the next piece of data that was propagated is below that set of data, starting on E60, then the next on E115, etc. The number of "sets" of data is different for each project I am working on. This is why I was using ActiveCells in the code, as I will have to offset from the active cell when I copy the next set of data. I plan on using a Do While statement to the code to reformat all of the data.

[Mayda89]

Oops, Now I got what you mean.
Meanwhile norie already posted the answer

[Norie]

The active cell in your code is E5, so instead of using ActiveCell I used Range("E5").

If you want the range to change use a range variable, set it to E5 at the start then offset it on each iteration of the loop.

Set rngCopy = Worksheets("Output").Range("E5")


Do 

    ' code to copy, using rngCopy 


   ' move rngCopy down 55 rows ready for next copy
   Set rngCopy = rngCopy.Offset(55)

While <whatever>

[lfwake2wake]

Awesome, that's what I was looking for. Thanks!