Macro Button to export Data to another workbook

[Closed]
Report
Posts
1
Registration date
Thursday September 19, 2013
Status
Member
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
wbTarget.Range("A1").Select

'clear existing values form target book
wbTarget.Range("A1:M51").ClearContents

'activate the source book
wbThis.Activate

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

'copy the range from source book
wbThis.Range("A12:M62").Copy

'paste the data on the target book
wbTarget.Range("A1").PasteSpecial

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

'save the target book
wbTarget.Save

'close the workbook
wbTarget.Close

'activate the source book again
wbThis.Activate

'clear memory
Set wbTarget = Nothing
Set wbThis = Nothing

End Sub.

Any Help on this would be great

Kind Regards
Ben

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!