Finding Range of one Workheet used in another

[parteegolfer]

I need to find the last row of worksheet A and use this as range for worksheet B. Workshhet A can change row count so I need B to canhange as well

Example:


VBA:
Range("A10").Select
Selection.Copy
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF('Recap Report'!R[-3]C2="""","""",'Recap Report'!R[-3]C2)"
Selection.Copy
Range("A10:A56").Select << I Need this value To be As many rows As In worksheet(Recap Report) >>
ActiveSheet.Paste

Any Suggestions?

[duane]

something like

myrow = Sheets("Recap Report").Range("A65536").end(xlup).row

assuming column A will contain the highest row

then in your code

Selection.Copy
Range(cells(10,1),cells(myrow,1).select
ActiveSheet.Paste

[Norman Jones]

Hi Partegolfer,

> I need to find the last row of worksheet A and use this as range for
> worksheet B. Workshhet A can change row count so I need B to canhange
> as well

Dim LRow As Long
Dim rng As Range


LRow = Sheets("A").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheets("B").Range("A10:A" & LRow)


---
Regards,
Norman



"parteegolfer" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last row of worksheet A and use this as range for
> worksheet B. Workshhet A can change row count so I need B to canhange
> as well
>
> Example:
>
>
> VBA:
> Range("A10").Select
> Selection.Copy
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = _
> "=IF('Recap Report'!R[-3]C2="""","""",'Recap Report'!R[-3]C2)"
> Selection.Copy
> Range("A10:A56").Select << I Need this value To be As many rows As In
> worksheet(Recap Report) >>
> ActiveSheet.Paste
>
> Any Suggestions?
>
>
> --
> parteegolfer
> ------------------------------------------------------------------------
> parteegolfer's Profile:
> http://www.excelforum.com/member.php...o&userid=31951
> View this thread: http://www.excelforum.com/showthread...hreadid=527414
>

[parteegolfer]

Duane,

I get a complie error in line - Range(cells(10,1),cells(myrow,1).select

any ideas why?

[parteegolfer]

Sorry its a Syntax error

Range(cells(10,1),cells(myrow,1).select

[Norman Jones]

Hi Parteegolfer,

I suspect that your reply was intended for Duane rather than me.

Try, however, adding a closing parenthesis:

Range(Cells(10, 1), Cells(myrow, 1)).Select

---
Regards,
Norman



"parteegolfer" <[email protected]>
wrote in message
news:[email protected]...
>
> Sorry its a Syntax error
>
> Range(cells(10,1),cells(myrow,1).select
>
>
> --
> parteegolfer
> ------------------------------------------------------------------------
> parteegolfer's Profile:
> http://www.excelforum.com/member.php...o&userid=31951
> View this thread: http://www.excelforum.com/showthread...hreadid=527414
>

[Tom Ogilvy]

If column A is being filled with formulas, unless the formulas are
overwriting existing values, it seems unlikely the extent of the report could
be determined from column A.

Just a thought.

--
Regards,
Tom Ogilvy


"duane" wrote:

>
> something like
>
> myrow = Sheets("Recap Report").Range("A65536").end(xlup).row
>
> assuming column A will contain the highest row
>
> then in your code
>
> Selection.Copy
> Range(cells(10,1),cells(myrow,1).select
> ActiveSheet.Paste
>
>
> --
> duane
>
>
> ------------------------------------------------------------------------
> duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
> View this thread: http://www.excelforum.com/showthread...hreadid=527414
>
>