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

[mphansen]

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

 

[PaddyD]

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]

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]

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]

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]

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]

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]

my VBA skills are effectively nil, sorry Plenty of VBAers here who should be able to help out...

Paddy