Selecting Data/Copy/Open workbook/Paste data in cell/Save

[jebelious]

Hi Folks

I have been pulling my hair out with this, i have found pieces of code around the place but this is just not working for me. to give you an idea, the code selects all of the data in x cells, copies the data, opens a workbook (master data sheet), finds the first blank cell in column A and pastes the data. Saves that workbook and closes it then selects the data from a2 downwards and deletes it.

Now the sheet that processes this macro is also linked to the master data spreadsheet to refresh the data for viewing purposes only, when i refresh the data in this i start to have issues.

It does all the steps up to save, once i do save it brings do you want to replace the data. even if i select yes it does not save the copied over data..


any help would be appreciated

Here is my code:

Sub Macro2()
'
' Macro2 Macro
'
If MsgBox("Only click Yes if you have finished the form, press No to stop.", vbYesNo) = vbNo Then Exit Sub
On Error GoTo Error
Range("A2").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select

Selection.Copy
Workbooks.Open Filename:="LOCATION-MasterData.xlsx"

Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(1).Cells
If Len(cell) = 0 Then cell.Select: Exit For
Next cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


ActiveWorkbook.Save
ActiveWorkbook.Close

Range("A2").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select

Selection.Delete
MsgBox ("Export Successful")
Exit Sub
Error:
MsgBox ("Export not successful, if this continues please contact me")

End Sub

 

[mumps]

Try deleting the line:

ActiveWorkbook.Save

and replace:

ActiveWorkbook.Close

with:

ActiveWorkbook.Close True

 

[jebelious]

Thanks for the quick reply -

A bit more context may elude to what is going wrong - Workbook 1 (with the macro) contains a data set which i want to copy and upload into workbook2 (this is a master data spreadsheet), Workbook 1 has a sheet that links via a datasource to workbook 2 and pulls through the data from there. If i do not refresh this the code does work, however as soon as i update/refresh the data this is where the error happens. and it wont save ( i attempted the above). When i go to try to open workbook 2 it is showing it as someone is in the file still and i can only open in read only.

 

[mumps]

Could you clarify what you mean by

Workbook 1 has a sheet that links via a datasource to workbook 2 and pulls through the data from there.

Also, what do you mean by "refresh"?

 

[jebelious]

Hi - Thanks for the reply. Will try to explain the full setup in a little more detail:

2 x Workbooks - No1 is the master data spreadsheet, No2 is something that multiple people can have to input data.

Workbook 2 - has headings in row 1, ref/level 1/ level 2/ date started/ date completed/ notes
Workbook 1 - has the same headings

People using workbook 2 will add entries into this workbook and will press the macro that copies all of the data they have entered, open workbook 1, and paste it in the first available blank cell in column A hence the original code (pasting removes all formatting in the master workbook 1).

Workbook 2 has a sheet within it which has a data connection setup to workbook 1 (master data), this is so a person can get a real time view of what exists in the master data workbook 1 for information purposes as people will be working on the data and closing off queries. When the table is refreshed on this sheet in workbook 2 i get the error i am talking about, pressing the macro now will not save the changes to workbook 1 even by getting the macro to accept the changes. When i go to open workbook 1 (with out closing workbook 2) it seems to think someone is still in workbook 1 even though the workbook is closed and the data has refreshed.

I am thinking when refreshing the data connection it is keeping a link open to workbook 1 and i perhaps need some code to end the connection after the refresh but i am unable to find anything that does this.

 

[mumps]

Thank you for the clarification. To be honest with you, I don't know what the problem may be. You may be right about the data connection. There doesn't seem to be anything wrong with the code you posted. The title of your current thread may be a little misleading as to the nature of your problem. Could I suggest that you start a new thread with an appropriate title describing the possible problem with the data connection. Hopefully, you will get a response that addresses your problem.