JBHP


I have an Excel spreadsheet that I need to copy a variable number of rows from 1 worksheet to another within the same spreadsheet (workbook).
I have a variable (NRRows) that contains the number of rows that I want to copy, ie: from row 1 torow "NRRows". I need to be able to select the entire set of rows at one time, copy them all at once and then paste them on another tab in the same spreadsheet.
How can I do this or for that matter how can I copy all the rows as well
The biggest problem I am having is that I cant figure out how to use the variable for the number of rows in a; Range, Rows, or Cells object.
Any help would be appreciatedSmile




Re: Using a Macro (VBA) to select a variable number of rows

Gnomie


Try using something like:



NRRows = 8
NRRowsRange = "1:" & NRRows

    Range("A4").Select
    ActiveCell.Rows(NRRowsRange).EntireRow.Select
    Selection.Copy
   
Range("A12").Select
ActiveSheet.Paste


 






Re: Using a Macro (VBA) to select a variable number of rows

Joerg Weser

I know what you mean - the proper syntax for these variable range references is not easily found.

Rows("1:" & NRRows).Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Range("A4").Select