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
Workbooks("template.xlsm").Worksheets("data").Range("A3:cellnum").Copy
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
Range("R:R").NumberFormat = "dd-mm-yyyy;@"
NewBook.SaveAs Filename:="d:\DiscoImport.csv", FileFormat:=6
End Sub