Hi there,
I need a macro that will copy all the data on the 'timetable' tab and insert it at the end of the table on the 'data' tab. ie the first empty row.
I have attached the file for viewing.
forum.ozgrid.com/index.php?attachment/36812/
Hi there,
I need a macro that will copy all the data on the 'timetable' tab and insert it at the end of the table on the 'data' tab. ie the first empty row.
I have attached the file for viewing.
forum.ozgrid.com/index.php?attachment/36812/
Re: Copy Data from one sheet and insert to the end of a table on another sheet
The following code will do what you want.
Option Explicit
Sub copyData()
Worksheets("Timetable").Select
Dim timeTableData As Range
Set timeTableData = Range("A2:O" & Range("A65000").End(xlUp).Row)
timeTableData.Select
Selection.Copy
Sheets("Data").Select
Dim lastRow As Integer
lastRow = Range("A65000").End(xlUp).Row + 1
Cells(lastRow, 1).Select
ActiveSheet.Paste
End Sub
Display More
Hope this helps
Anthony
Re: Copy Data from one sheet and insert to the end of a table on another sheet
Hi
It works perfectly, exactly what i wanted.
Just wondering about this other question.
What would be the code to transfer the 'timetable' sheet from one workbook to the 'data' sheet in another workbook.
Re: Copy Data from one sheet and insert to the end of a table on another sheet
The process is fairly much the same. For this to work it assumes that the second workbook is open at the same time as the first workbook. Just substitute the workbook name for the second workbook into the following code.
Option Explicit
Sub copyDataToAnotherBook()
Worksheets("Timetable").Select
Dim timeTableData As Range
Set timeTableData = Range("A2:O" & Range("A65000").End(xlUp).Row)
timeTableData.Select
Selection.Copy
'Substitute Another Data.xlsx with the name of the second workbook
'This second workbook must be open at the same time
Windows("Another Data.xlsx").Activate
Sheets("Data").Select
Dim lastRow As Integer
lastRow = Range("A65000").End(xlUp).Row + 1
Cells(lastRow, 1).Select
ActiveSheet.Paste
End Sub
Display More
Hope this helps
Anthony
PS: If the other workbook is closed you can do the following
Re: Copy Data from one sheet and insert to the end of a table on another sheet
Ok i will try it out should work.
I need to paste the cells as values.. when i use PasteSpecial (xlPasteValues) instead of Paste, it gives an error.
Re: Copy Data from one sheet and insert to the end of a table on another sheet
You can paste as values but to preserve the format you will need to use the following
Option Explicit
Sub copyData()
Worksheets("Timetable").Select
Dim timeTableData As Range
Set timeTableData = Range("A2:O" & Range("A65000").End(xlUp).Row)
timeTableData.Select
Selection.Copy
With Sheets("Data").Range("A65000").End(xlUp).Offset(1, 0)
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
End With
End Sub
Display More
Is that what you need? If you dont keep the xlPasteFormats command you will lose the format of your dates and times in the spreadsheet. If I am missing want you want let me know in more detail.
Hope this is what you want
Anthony
Don’t have an account yet? Register yourself now and be a part of our community!