Copy/Paste/Value macro with Command Button (beginner)

mphansen

New Member
Joined
Jun 23, 2002
Messages
45
Hi everyone, I've tried searching the message board to find an answer to my question but to no avail (I may have even stumbled on a solution, but as VB is a relatively foreign language to me, it's a tad difficult making any sense of the code), Anyway, my question

Using VB in a file named "Test" in Sheet1, is there any way I can have a command button, that when clicked, will copy/paste/value from a closed workbook sheet (i.e. named "Data") and dump the values from "Data" to Sheet2 in "Test" ?

Note: the entire sheet of "Data" does not necessarily have to be copied...from an efficiency standpoint, the range of values to copy in "Data" could be limited to $1:$500.

Any insight appreciated.

Mark
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi!

Take a look at Dave Hawley's 'method for getting data out of closed workbooks' (on the VBA section of his site under 'Extract from Workbook'):

http://www.ozgrid.com/

& see if you can adapt it to your neds.

paddy
 

mphansen

New Member
Joined
Jun 23, 2002
Messages
45
Hi PaddyD - I tried a dummmy test & it worked.

Couple outstanding items if you don't mind taking a look at:

1.What do I have to do to make it work with a command button ?
2.How can I have the data also copy the closed book's sheet formatting ?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
1.What do I have to do to make it work with a command button ?
2.How can I have the data also copy the closed book's sheet formatting ?

1) add the button to the sheet, right click on it & view code. insert the appropriate call in the click() event:<pre>
Private Sub CommandButton1_Click()
Run "PullInSheet1"
End Sub</pre>


This replaces the Workbook_Open() sub in the example.


2) The original code is as follows:<pre>

Sub PullInSheet1()
'''''''''''''''''''''''''''''''
'Written By OzGrid Business Applications
'www.ozgrid.com

'Pulls in all data from sheet1 of a closed workbook.
''''''''''''''''''''''''''''''''

Dim AreaAddress As String

'Clear sheet ready for new data
Sheet1.UsedRange.Clear
'Reference the UsedRange Address of Sheet1 _
in the closed Workbook.
Sheet1.Cells(1, 1) = "= 'C:My DocumentsOzGrid" _
& "[Book1.xls]Sheet2'!RC"
'Pass the area Address to a String
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Range(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not _
empty the pull in it's content, else put in an Error.
.FormulaR1C1 = "=IF('C:My DocumentsOzGrid" _
& "[Book1.xls]Sheet1'!RC="""",NA(),'C:My Documents" _
& "OzGrid[Book1.xls]Sheet1'!RC)"
'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only
.Value = .Value
End With
End Sub</pre>


I guess you'd have to add a bit that copied the format in the source range in its entirity to the destination.

Unfortunately, my VBA skills are poor - don't even know if you'd have to open the workbook to do it or whether something like Selection.PasteSpecial Paste:=xlFormats would work.

Plenty of great VBAers here though...

Paddy
This message was edited by PaddyD on 2002-09-19 19:27
 

mphansen

New Member
Joined
Jun 23, 2002
Messages
45

ADVERTISEMENT

PaddyD - perhaps I should have told you the command button resides on Sheet2 of Book2.xls

Would that change the code below ? As it stands now the code for the command button doesn't seem to work following your steps.

Thanks - Mark

1) add the button to the sheet, right click on it & view code. insert the appropriate call in the click() event:


Private Sub CommandButton1_Click()
Run "PullInSheet1"
End Sub
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
1) Give the button a name, e.g 'GetData'
2) Open the VBA editor (Alt + F11)
3) Click on sheet2 object
4) Paste in code:

<pre>

Private Sub CommandButton1_Click()

If CommandButton1.Caption = "GetData" Then
Run "PullInSheet1"
End If

End Sub


</pre>
 

mphansen

New Member
Joined
Jun 23, 2002
Messages
45
PaddyD - one last time, promise!

When I run macro w/o the command box - everything works smoothly.

Running the macro using the command box places #N/A now in any blank areas w/in the range ....why is that doing that ?

Can you take a look at the code --maybe you'll see an error that I'm not.

"This Workbook" has the following code:

Private Sub Workbook_Open()
Run "PullInReturns()"
End Sub

Sheet1 has the following code:

Sub PullInReturns()
'''''''''''''''''''''''''''''''
'Written By OzGrid Business Applications
'www.ozgrid.com

'Pulls in all data from sheet1 of a closed workbook.
''''''''''''''''''''''''''''''''

Dim AreaAddress As String

'Clear sheet ready for new data
Sheet1.UsedRange.Clear
'Reference the UsedRange Address of Sheet1 _
in the closed Workbook.
Sheet1.Cells(1, 1) = "= 'E:mhansenHold and Delete" _
& "[Manager Analysis Macro.xls]ReturnData'!RC"
'Pass the area Address to a String
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Range(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not _
empty the pull in it's content, else put in an Error.
.FormulaR1C1 = "=IF('E:mhansenHold and Delete" _
& "[Manager Analysis Macro.xls]ReturnData'!RC="""",NA(),'E:mhansen" _
& "Hold and Delete[Manager Analysis Macro.xls]ReturnData'!RC)"
'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only
.Value = .Value
End With
End Sub

Sheet2 has the following code:

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "GetData" Then
Run "Sheet1.PullInReturns()"
End If
End Sub

In sheet2 if I remove "Sheet1." from "Sheet1.PullInReturns()", I get a Run-time error - The macro PullInReturns() cannot be found

The Macro in macro name dialog box is called Sheet1.PullinReturns

Any thoughts ?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
my VBA skills are effectively nil, sorry :( Plenty of VBAers here who should be able to help out...

Paddy
 

Forum statistics

Threads
1,147,451
Messages
5,741,200
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top