-
Dec 30th, 2009, 04:13 PM
#1
Thread Starter
Member
[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
-
Dec 30th, 2009, 04:24 PM
#2
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
-
Dec 30th, 2009, 05:16 PM
#3
Thread Starter
Member
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
-
Dec 30th, 2009, 11:22 PM
#4
Re: Setting a Sheet after Opening
best way is
vb Code:
Workbooks.Open Filename 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
-
Dec 31st, 2009, 07:21 PM
#5
Thread Starter
Member
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?
-
Dec 31st, 2009, 11:29 PM
#6
Re: Setting a Sheet after Opening
vb Code:
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
-
Jan 1st, 2010, 10:50 AM
#7
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|