How to use a variable value to set the range to copy


I have been googling for a while but not able to find a solution, hoping someone can help.

I'm trying to copy multiple column of data from one worksheet (Sheet1) to a new worksheet (Sheet2), the start of the range always remain the same (Column A, Row 3) but the ending column changes according to the Row. I need this a macro so it can be assigned to a button, currently my code will display the value (second column ending row) in a seperate cell but.

Eg: I need to copy all data from A3 to U3 to a new worksheet but the next time the range maybe from A3 to E4 as more data is inputed. The columns will alway remain the same (A & E) but the row will be different each time.

Please help

Sub CopyItOver()
    Dim lastRow As Long
    Dim CellNum As String
    RowLetter = "U"
    lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    CellNum = RowLetter & lastRow
    Worksheets("data").Range("$H$2").Value = CellNum
    Set NewBook = Workbooks.Add
    NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
    Range("R:R").NumberFormat = "dd-mm-yyyy;@"
    NewBook.SaveAs Filename:="d:\DiscoImport.csv", FileFormat:=6
End Sub



You need this: Copy one range and paste in another range

and this: Find the Next Blank Row with VBA Macros in Excel

Building the macro should be pretty easy to do once you read those tutorials. I can help you with the macro a bit more but not until tomorrow.


Answer the Question

You must create an account to use the forum. Create an Account or Login