Extracting Data to another Workbook

**nnaida** · 11-19-2006, 08:27 PM

I am currentyly using VB to extract some data sets to another tab in the same workbook. I would like to extract the same data to a new workbook instead upon running the macro. The code I am currently using is:

Sub RunModel()

Dim Events As Range
'
'
Application.ScreenUpdating = False

Set Events = Worksheets("Inputs").Range("L41:L90")
Worksheets("Results").Range("A5:FX5004").Clear
Worksheets("Results").Range("A5009:FX10010").Clear

y = -100

For x = 1 To 50

If Events(x) = "N" Then
GoTo 10
Else

Worksheets("Data").Range("D5").Value = x

Calculate

y = y + 100

Range("RResults").Copy
Worksheets("Results").Cells(y + 5, 1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range("FResults").Copy
Worksheets("Results").Cells(5009 + y, 1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

End If

10 Next x

Application.ScreenUpdating = True

End Sub

**duane** · 11-19-2006, 08:48 PM

this will create a new workbook - then refer to this name when you do your pastes

Workbooks.Add
ActiveWorkbook.SaveAs FileName:= _
"C:\put your file name with directory here.xls"

**davesexcel** · 11-19-2006, 08:51 PM

this is what you would do to clear contents in another workbook

Workbooks("test").Sheets("Report").Range("A5:FX5004,A5009:FX10010").ClearContents

**nnaida** · 11-19-2006, 10:08 PM

I'm still receiving an error when trying to copy this bit.

Range("RResults").Copy
Workbooks("C:\Summaries\Summary.xls").Worksheets("Results").Cells(y + 5, 1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

**davesexcel** · 11-19-2006, 10:10 PM

Try this, if you are using the workbooks way

Range("RResults").Copy
Workbooks("Summary").Worksheets("Results").Cells(y + 5, 1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

**nnaida** · 11-19-2006, 10:40 PM

I get "selected method of range class failed"

Do I need to specify a directory when using this function, or does the Workbook need to be open to copy results?

Thanks again.