Difficulty Copying from Workbook to Workbook

[Mike54]

When I copy from one Workbook to another things work fine, but when I record it as a Macro it works as I do it but will not replay.

I have tried many ways and spent lots of time hunting for solutions, many are not relevant or too complicated

I'm trying to collect "Month Data" I send out a Workbook which contains a Macro.

When it's sent back I have the "Year15" Workbook open, I open the "September" Workbooks one at a time and run the Macro to import the Data to Column A

This must have been done successfully many times, but I continually get error messages

Any guidance much appreciated
Many thanks
PS
I think i have finally managed to post HTML for first time! Yippeee


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()<br><SPAN style="color:#007F00">'Workbook Name "September" this is my source data</SPAN><br><br><SPAN style="color:#007F00">'MData Name of Range("A1:A20)</SPAN><br>Application.Goto Reference:="MData"<br>Selection.Copy<br><br><SPAN style="color:#007F00">'Separate Workbook Named "Year15" this is my destination</SPAN><br>Windows("Year15.xlsm").Activate<br><br><SPAN style="color:#007F00">'This will be the first blank Cell Column A, but for now A24 will do.</SPAN><br>Range("A24").Select<br>ActiveSheet.Paste<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Rijnsent]

Hi Mike,
congrats on the HTML post, next step: use the [ code ] sign for the bits of code . I took the liberty of rewriting your code a bit, using some variables to get rid of .Select and .Activate statements which generally have a high chance of causing errors. I also threw in "find last row".

Private Sub CommandButton2_Click()
'Workbook Name "September" this is my source data

Set ActWb = ActiveWorkbook
Set DataSht = ActWb.Names("MData").RefersToRange.Worksheet
Set DestWb = Workbooks("Year2015.xlsm")

lastrw = DestWb.ActiveSheet.Range("A" & Cells.Rows.Count).End(xlUp).Row
DataSht.Range("MData").Copy Destination:=DestWb.ActiveSheet.Range("A" & lastrw)

End Sub

Hope this helps you,
Koen

 

[Mike54]

Koen

Thank you for taking the time to help me, I had not considered using variables in that way and if it reduces errors the I think I need to use your method. As you can see I eventually 'Forced' a method, it got me through at the time but I'm not sure it's very robust, although finding the last entry from the bottom up seemed to work well.
I have a little time before the next one goes out so I will switch to your method, thanks for your time & help
Mike

[<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> L <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>****Application.Goto Reference:="AllDataSept"<br>****Selection.Copy<br>****Windows("SeptemberMaster 1.0.xlsm").Activate<br>****<SPAN style="color:#007F00">'Worksheets("Sheet1").Activate</SPAN><br>****L = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row<br>****L = L + 1<br>****ActiveSheet.Cells(L, 4).Select<br>****ActiveSheet.Paste<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>]