Using variable to define a range

[Lou]

Hi. I am able to create a macro to identify the next available row

'Nextrow = "A" & ActiveSheet.Range("A65536").End(xlUp).Row + 1

This will tell me the actual cell number.

The tricky thing is, for me at least is how can I write something that says to paste my data into that range. I can't just write Range("nextrow")

My simple code is Range("Data").copy Range("nextrow")

[lucas]

Here is one that uses an array and input box for you to examine:
Option Explicit
Option Base 1
Sub Enter_Agreement_details()
Dim Iput As String, arrData As Variant, i As Integer
Dim NextRow As Long

NextRow = Sheet3.Range("A65536").End(xlUp).Row + 1

arrData = Array("Agreement Number", "Parent Company", _
"Title", "First Name", "Surname", "Street", _
"Town", "City", "County", "Post Code", "Phone", _
"Fax", "E-Mail", "Application Received", "UNA To Site", _
"UNA Received From Site", "UNA To DEFRA", "DEFRA Approved")

For i = LBound(arrData) To UBound(arrData)

Iput = InputBox(".:: p l e a s e e n t e r f o l l o w i n g d a t a ::. " & _
Chr(10) & Chr(10) & arrData(i), arrData(i))

Sheet3.Cells(NextRow, i) = Iput
Next i

End Sub



The line you are looking for in the code above is:
Sheet3.Cells(NextRow, i) = Iput

[Lou]

Hi. I've tried to figure out how that code relates to what I need by I can't.

[lucas]

Hi Lou,
Could you post what you're working on? It would keep us from guessing and just throwing examples at you until we find what you're trying to do.

[Lou]

Hi. I want to copy an entire worksheet. No special parameters or exceptions from one sheet to another sheet. I want to paste it to the first blank row on the sheet

Sheet1- Contains a range called retailers, this is the data I want copied
Sheet2- Where I want the data copied to (first blank row)

Here is the code. I basically believe I am stuck on the last part, how to indicate where I want to begin pasting the data

Sub Nextrow()
Dim Nextrow As Variant
Nextrow = "A" & ActiveSheet.Range("A65536").End(xlUp).Row + 1
MsgBox Nextrow
Range("Q1").value = Nextrow
Range("Retailers").Copy (Want to paste to cell value of Nextrow)

End Sub

[unmarkedhelicopter]

You shouldn't use hard coded values like "A65536" as that is not a limit (bottom row) in XL2007 so use something like "cells(activeworksheet.rows.count, 1)"

[lucas]

Lou, I apologize. I have gotten busy and will have to revisit this. Maybe someone will come along and finish this if I don't get back today.

[mdmackillop]

Some code I made up earlier. Adjust to suit.
Sub MoveSheets()
Dim i As Long, k As Long
For i = 2 To sheets.count
k = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 2
Sheets(i).UsedRange.Copy Sheets(1).Cells(k, 1)
Next
End Sub

[Paul_Hossler]

Technique I use --



Option Explicit
Function NextAvailRow(ws As Worksheet) As Long
Dim r As Range

Set r = ws.Cells(ws.Rows.Count, 1).End(xlUp)
If r.Row > 1 Then
NextAvailRow = r.Offset(1, 0).Row
Else
NextAvailRow = r.Row
End If
End Function

Sub Test()

'I deleted blank row 1 on the source sheet
Call Worksheets("Sheet1").Cells(1, 1).CurrentRegion.Copy( _
ActiveSheet.Cells(NextAvailRow(ActiveSheet), 1))
End Sub