Open closed workbook and copy new datas all the way down.

[asyamonique]

Good Day,
I have datas on ranges (AL2:AO53)with formulas.
Is it possible to send those datas into the another closed workbook.
The command button located on worksheet and target ranges will be AW2
when the button has been click on second time the new datas will be paste on following cells and it will continue all the way down.
Many Thanks

 

[Trevor G]

Do you want the data to be copied as well as the formula or the data copied into the other workbook as data not including the formula (so just values).

Try this out

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> copyData1()<br><SPAN style="color:#00007F">Dim</SPAN> wkbk <SPAN style="color:#00007F">As</SPAN> Workbook <SPAN style="color:#007F00">'Declare wkbk</SPAN><br>wkbk = Range("AW2") <SPAN style="color:#007F00">'Cell has path and workbook name including file extension</SPAN><br>Range("(AL2:AO53").Copy<br>Workbooks.Open wkbk <SPAN style="color:#007F00">'Open workbook</SPAN><br>Sheets("Sheet1").Activate <SPAN style="color:#007F00">'Change sheet name</SPAN><br>Range("A1").Select <SPAN style="color:#007F00">'Change cell id if required</SPAN><br>Selection.End(xlDown).Select<br>ActiveCell.Offset(1, 0).Select<br>ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats<br>ActiveWorkbook.Save<br>ActiveWorkbook.Close<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[asyamonique]

Range("AL2:AO53").Select
    Selection.Copy
    Workbooks.Open Filename:="S:\Entries\Test Entries.xls"
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    ActiveWorkbook.Save
    ActiveWindow.Close

Trevor ,
This is the code simply created with 'Record Macro' function.And its copying the datas into the closed
workbook at given ranges.But i want the new datas will be copied all the way down continiously.
With your code i can't do that..
Many thanks.

 

[Trevor G]

Do you mean that the cells you copied have to be pasted but after the last cell in column A that has data in it?

If so then the code I supplied you should work, but you would need to make sure the cell that has the workbook name in it has the full name including the .xls extension.
If you mean that you want the cells selected that has data in it even if it gets more than you quoted then you can use currentRegion.

Sub copyData1()
Dim wkbk As Workbook 'Declare wkbk
wkbk = "S:\Entries\Test Entries.xls" 'Range("AW2") 'Cell has path and workbook name including file extension
Range("(AL2:AO53").Copy
Workbooks.Open wkbk 'Open workbook
Sheets("Sheet2").Activate 'Change sheet name
Range("A1").Select 'Change cell id if required
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub