Results 1 to 7 of 7

Thread: [RESOLVED] Setting a Sheet after Opening

  1. #1

    Thread Starter
    Member
    Join Date
    May 2009
    Posts
    49

    Resolved [RESOLVED] Setting a Sheet after Opening

    My Code only works up to the open work sheet. After that Nothing happens

    I think not ativating the Sheets Porperly.

    I was hopeing it would run somthing like this

    Open workbook 2
    Copy specified Ranage in Workbook 2
    Paste in in a specific Range in Workbook1 ( this is the work book the Form an VBA is in.
    Repeat the Copy and Paste thur the array. Here is what I got.

    Code:
    Private Sub BuildSAS_Click()
     
     
    Dim UserformStr(0 To 9) As String 'Array of TExtvalues
    UserformStr(0) = "OECTx.Text"
    UserformStr(1) = "Bectx.Text"
    UserformStr(2) = "BRCtx.Text"
    UserformStr(3) = "Buctx.Text"
    UserformStr(4) = "LGCtx.Text"
    UserformStr(5) = "PLCtx.Text"
    UserformStr(6) = "LTCtx.Text"
    UserformStr(7) = "CVCtx.Text"
    UserformStr(8) = "APCTX.Text"
    UserformStr(9) = "SMCtx.Text"
    
    Dim CompCopyRng(0 To 9) As String 'Array of Concatinated Value for Range use
    CompCopyRng(0) = "OECTx.Text & FirstRow.Text"
    CompCopyRng(1) = "Bectx.Text & FirstRow.Text"
    CompCopyRng(2) = "BRCtx.Text & FirstRow.Text"
    CompCopyRng(3) = "Buctx.Text & FirstRow.Text"
    CompCopyRng(4) = "LGCtx.Text & FirstRow.Text"
    CompCopyRng(5) = "PLCtx.Text & FirstRow.Text"
    CompCopyRng(6) = "LTCtx.Text & FirstRow.Text"
    CompCopyRng(7) = "CVCtx.Text & FirstRow.Text"
    CompCopyRng(8) = "APCTX.Text & FirstRow.Text"
    CompCopyRng(9) = "SMCtx.Text & FirstRow.Text"
    
    Dim Filename As Variant
    Filename = MMatrixtx.Text
    Workbooks.Open Filename
    Worksheets("Matrix").Activate
    Dim i As Integer
    Dim j As Integer
    For i = 0 To 9
        For j = 0 To 9
    If UserformStr(i) = "" Then
    GoTo Line1
           Sheets("Matrix").Range(CompCopyRng(j), Sheets("Matrix").Range("UserformStr(i)" & 65536).End(xlUp)).Copy
                ThisWorkbook.Worksheets("SAS").Activate
                Range("P9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            End If
    Line1:     Next j
    Next i
    End Sub

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Setting a Sheet after Opening

    you may need to specify which workbook the sheet is in, otherwise it tries, to find the sheet in the same workbook

    better to set a worksheet object to work with
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    May 2009
    Posts
    49

    Re: Setting a Sheet after Opening

    I was trying to Do it Like this but i would get a Run time error

    Workbooks(Filename).Worksheets("Matrix").Activate

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Setting a Sheet after Opening

    best way is
    vb Code:
    1. Workbooks.Open Filename
    2. set ws = Worksheets("Matrix")
    then work with the ws worksheet object

    your code should have been
    workbooks(workbookname).sheets("Matrix").activate
    only requires the filename, not the path
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Member
    Join Date
    May 2009
    Posts
    49

    Re: Setting a Sheet after Opening

    I get a runtime error 13 follow the stippit you posted Do I need to do someting befor that I did Dim WS as Worksheets?

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Setting a Sheet after Opening

    vb Code:
    1. Dim WS as Worksheet ' no s
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    May 2009
    Posts
    49

    Re: Setting a Sheet after Opening

    Thanks this fixed it.

    Resolved

Posting Permissions

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



Click Here to Expand Forum to Full Width