Macro Button to export Data to another workbook

Registration date
Thursday September 19, 2013
Last seen
September 19, 2013
Hi All

I've been having difficulties trying to apply a macro to a button that submits data and exports into another sheet. One issue I've come accross is that the i need to copy cells D5:D17 and the data to be exported into rows A2:Q2. Also the exported data needs to not overwrite any previous exported data so it is automatically exported to the row below the previous input.

Input: Money back form.xlsx
Sheet: Input
Target:Book 2.xlsx
Sheet: Output

I found an ideal formula but can't get it to work and seems to reject it (Probably my own stupidity), plus I forsee problems with the overwriting and row problem being an issue even if I can get it to work.

Sub CopyOpenItems()
' CopyOpenItems Macro
' Copy open items to sheet.
' Keyboard Shortcut: Ctrl+Shift+O
Dim wbTarget As Workbook 'workbook where the data is to be pasted
Dim wbThis As Workbook 'workbook from where the data is to copied
Dim strName As String 'name of the source sheet/ target workbook

'set to the current active workbook (the source book)
Set wbThis = ActiveWorkbook

'get the active sheetname of the book
strName = ActiveSheet.Name

'open a workbook that has same name as the sheet name
Set wbTarget = Workbooks.Open("C:\filepath\" & strName & ".xlsx")

'select cell A1 on the target book

'clear existing values form target book

'activate the source book

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False

'copy the range from source book

'paste the data on the target book

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False

'save the target book

'close the workbook

'activate the source book again

'clear memory
Set wbTarget = Nothing
Set wbThis = Nothing

End Sub.

Any Help on this would be great

Kind Regards

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!