+ Reply to Thread
Results 1 to 9 of 9

Extracting Data to another Workbook

  1. #1
    Registered User
    Join Date
    11-15-2006
    Posts
    7

    Extracting Data to another Workbook

    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

  2. #2
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    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"
    not a professional, just trying to assist.....

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    this is what you would do to clear contents in another workbook
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-15-2006
    Posts
    7
    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

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    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

  6. #6
    Registered User
    Join Date
    11-15-2006
    Posts
    7
    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.

  7. #7
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    you either need to have the macro open the destination files, or to create the destination file before you execute the copy and paste

  8. #8
    Registered User
    Join Date
    11-15-2006
    Posts
    7
    I have created the new worksheet manually and I am receiving range errors (9) in the paste section. Not really sure if its syntax or what.




    Sub RunModel()

    Dim Events As Range
    '
    '
    Application.ScreenUpdating = False

    Set Events = Workbooks("Data").Worksheets("Inputs").Range("L41:L90")



    y = -100

    For x = 1 To 50


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

    Workbooks("Data.xls").Worksheets("ENGINE").Range("D5").Value = x

    Calculate

    y = y + 100

    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


    Range("TResults").Copy
    Workbooks("C:\Summaries\Summary.xls").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

  9. #9
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Here is an example:
    This code is in workbook called "test2"
    It copies the first row in sheet1 and pastes it to the first empty row in workbook(test).Sheets(Sheet1).ColumnA

    if workbook test is not open it will open it

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1