0

I need to create a new Worksheet in Workbook1 and name it according to a cell value. I then need to copy and paste a value from Workbook1 into Workbook2 in order to run a model. I then need to copy and paste the outputs of Workbook2 into the new worksheet i just created in Workbook1. I then need to repeat this task until i have gone through all of entries in my list in Workbook1.

I keep running into multiple problems along the way.

The main problem i have is that the macro is having trouble selecting the value to copy and paste. I keep getting runtime error 9 and cannot resolve it.

I have created a Do Loop to try and complete all of the steps listed above.

I have also created a Do Loop inside of a Do Loop to produce the new Worksheet. This creates the tab but has placed the worksheet in both of the different workbooks following my attempts to rectify the error.

I also do not know how to refer to the new tab that i have created in the subsequent code so have written a placeholder called "NEW TAB" to signify this

The range of the outputs that is created by Workbook2 is different each time the model is run so i am trying to copy and paste 500,000 rows of data between workbooks each time. An 1004 error was produced saying this was too many rows. 

' Variable Declaration

Dim Ws As Worksheet
Set Ws = Sheets("Universe")
Dim WorksheetNumber As Integer
WorksheetNumber = 1

'Open Workbook2
Workbooks.Open "Workbook2"

' Create Do Loop
    ' Create New Worksheet in Workbook1
ThisWorkbook.Activate
Ws.Range("B11").Select
Do While ActiveCell.Value <> ""
    intTimes = intTimes + 1

    If ActiveCell.Value > 0 Then

        ' Create Output Sheet
        With ThisWorkbook.Sheets.Add(, ActiveSheet)
            Do
                .Name = WorksheetNumber
                If Err = 1004 Then
                    WorksheetNumber = WorksheetNumber + 1
                    Err.Clear
                Else
                    Exit Do
                End If
                DoEvents
            Loop
        End With

        ' Enter Value into Workbook2 from Workbook1
        ThisWorkbook.Sheets("Universe").Range("O11").Copy Destination:=Workbooks("Workbook2").Sheets("Model").Range("E9")

        ' Copy and Paste Column D from Workbook2 to Workbook1
        Workbooks("Workbook2").Worksheets("Model").Range("D14:D500000").Copy
        Workbooks("ThisWorkbook").Worksheets("NEW TAB").Range("D2").PasteSpecial Paste:=xlPasteValues

        'Copy and Paste Column G from Workbook 2 to Workbook1
        Workbooks("Workbook2").Worksheets("Model").Range("G14:G500000").Copy
        Workbooks("ThisWorkbook").Worksheets("NEW TAB").Range("E2").PasteSpecial Paste:=xlPasteValues

        'Copy and Paste Remaining Details from Workbook2 to Workbook1
        Workbooks("Workbook2").Worksheets("Model").Range("L14:U500000").Copy
        Workbooks("ThisWorkbook").Worksheets("NEW TAB").Range("F2").PasteSpecial Paste:=xlPasteValues

    End If

    ActiveCell.Offset(1, 0).Select

    If ActiveCell.Value = "" Then
        intTimes = 0

    End If

Loop

The expected result is that a new tab will be created each time the model is run with the outputs from the model pasted into it each time. There will be up to 500 new tabs created (which will then need to be consolidated into a single tab).

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
ChrisB
  • 103
  • 1
  • 1
  • 5
  • 1
    You are asking too many things at once. Break your project into pieces, try to solve each piece on its own. Ask *one* clear question (telling which error you get and in which line). • Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). and don't use `.Select` or `.Activate`. – Pᴇʜ Mar 29 '19 at 12:49
  • .Name = WorksheetNumber, you are setting the name to be the worksheet number, so say `Worksheets(WorksheetNumber)` Also, look at using a range and looping through the cells, rather than offset, select. `set r=range("a1:a1000"):for each c in r.cells:..........:next c` – Nathan_Sav Mar 29 '19 at 12:52
  • Ok, thanks for the feedback. I'll try to make some progress and then post questions in smaller chunks. – ChrisB Mar 29 '19 at 14:57

0 Answers0