Prev: To insert a picture in a form
Next: Type mismatch
From: StonyfieldRob on 2 Apr 2010 08:51
Trying to copy a specific range on a worksheet to another workbook. The
source workbook is not always named the same so need it to be a bit generic
like the code below. The only catch is I need to paste values only and no
formulas.

Sub CopyData()
'need online help to paste special
Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet

Set SourceWB = Workbooks.Open("\\Wuslonfs03\share\product\Demand
Planning\DPDatabases\AbyA.xls")
Set DestinationWB = ThisWorkbook
With SourceWB
Set DestinationWS = DestinationWB.Worksheets("Any by Any")
With .Worksheets("Any by Any")
.Range("A1:AC65536").Copy
End With

End With

End Sub

From: tompl on 2 Apr 2010 10:05
This is how I would do it:

Sub CopyData()
'need online help to paste special
Dim SourceWB As Workbook
'Dim DestinationWB As Workbook
'Dim DestinationWS As Worksheet

Set SourceWB = Workbooks.Open("\\Wuslonfs03\share\product\Demand
Planning\DPDatabases\AbyA.xls")

'Set DestinationWB = ThisWorkbook
'With SourceWB
' Set DestinationWS = DestinationWB.Worksheets("Any by Any")
' With .Worksheets("Any by Any")
' .Range("A1:AC65536").Copy
' End With
'
'End With

SouceWB.Worksheets("Name of Worksheet to be copied"). _
Range("A1:AC65536").Copy
ThisWorkbook.Worksheets("Any by Any").Range("A1"). _
PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

Note: Sometimes I think that extra variables just get in the way, but I
don't really know your intent so you should use them as you see fit.

Tom
 | 
Pages: 1
Prev: To insert a picture in a form
Next: Type mismatch