Copy Data from one sheet and insert to the end of a table on another sheet

tinadalrin · Mar 8th 2011

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/

smuzoen · Mar 9th 2011

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.

Code

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

tinadalrin · Mar 9th 2011

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.

smuzoen · Mar 9th 2011

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.

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

Code

Workbooks.Open Filename:="C:\Path to second workbook.xlsx", _
        UpdateLinks:=0

tinadalrin · Mar 9th 2011

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.

smuzoen · Mar 9th 2011

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

Code

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

Copy Data from one sheet and insert to the end of a table on another sheet

Participate now!

Share