Solved

Copy Paste Error Using RefEdit Control VBA Excel

Posted on 2016-08-31
3
39 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
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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows 7 Share with concurrent edits(Excel) 3 33
Control calculations in Excel using VBA 4 20
Help with Excel formula 6 38
Dynamic Filter ? 4 21
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now