Solved

Copy Paste Error Using RefEdit Control VBA Excel

Posted on 2016-08-31
3
87 Views
Last Modified: 2016-08-31
I want to delete and reorder columns in a downloaded csv file, then copy the remaining data into a sheet in another, master workbook.

Procedure:
Open both attached spreadsheets
With The Sample_PASTE_FROM workbook active, CTRL+SHIFT+S to run the macro
With the Ref edit user-form, click Range("A2") in the Sample_PASTE_TO spreadsheet

I have succeeded right up until the Copy and paste command.

This is the code from the Ref edit Userform OK click command
Private Sub cmd_OK_Click()
'This will paste the copied data from the Braintree
' Settlement Batch Summary Report
'-------
'Declare Variables
    Dim rBraintree As Range     'First Cell in Braintree tab to paste info.
    Dim strAddress As String    'Ref Edit address
'-------
'Address of paste area from refedit control
    strAddress = ref_Recon.Value
'Set Paste Range
    Set rBraintree = Range(strAddress)
'Activate the second workbook to paste the data
    rBraintree.Parent.Activate
'Select the paste destination cell
    rBraintree.Select
'Paste the data
    ActiveSheet.Paste
'Reset the ref_Recon refedit control
    Unload Me

End Sub

Open in new window


It seems that the CutCopyMode does not remain active when passing from the module with the code to the Userform.

Can someone help, please?

Thanks very much
David Phelops
Sample_PASTE_TO.xlsx
Sample_PASTE_FROM.xlsm
0
Comment
Question by:David Phelops
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 250 total points
ID: 41777993
Why don't you place the Copy statement within the OK event code, prefixing/fully-qualifying as follows?...

ThisWorkbook.ActiveSheet.Cells(1, 1).CurrentRegion.Offset(1, 0).Copy.Cells(1, 1).CurrentRegion.Offset(1, 0).Copy
1
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 41777994
Hi,

pls try
Private Sub cmd_OK_Click()
'This will paste the copied data from the Braintree
' Settlement Batch Summary Report
'-------
'Declare Variables
    Dim rBraintree As Range     'First Cell in Braintree tab to paste info.
    Dim strAddress As String    'Ref Edit address
'-------
'Address of paste area from refedit control
    strAddress = ref_Recon.Value
'Set Paste Range
    Set rBraintree = Range(strAddress)
'Activate the second workbook to paste the data
    rBraintree.Parent.Activate
'Select the paste destination cell
    rBraintree.Copy
'Paste the data
    ActiveSheet.Paste
'Reset the ref_Recon refedit control
    Unload Me

End Sub

Open in new window

Regards
1
 

Author Closing Comment

by:David Phelops
ID: 41778018
Thank you.  Putting the Copy command in the OK code worked. A Happy chappie!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question