Run-time error '9':

[ednance]

I am getting this error when I run this macro. Can someone help me fix this error? Thank you

Sub Rollup()
'
' Rollup Macro
'
' This macro is used to roll up data from other excel
' files in DEPTSUM.xls. This rountine assume that the
' data in all the file are of the same format. If the format of
' either target file or input files change this routine
' would need to be tweaked.
'
' Created on 03/30/1999
'

Dim strCurFile As String
Dim Cell As Range
Dim count As Integer
Dim x As Object
Dim rngb As Integer
Dim rngc As Integer
Dim tempval As String
Dim month As String
Dim name As String
Dim conStrFile As String
Dim colval As Integer


colval = 3
rngc = 5

Sheets("Summary").Select

Range("A5").Select
month = ActiveCell.Value
Range("a1").Select
conStrFile = ActiveCell.Value
conStrFile = conStrFile & ".xls"
' Loop through list of location numbers
' until a blank cell is reached


For Each Cell In Range("C80:Bv80")
If Cell.Value <> "" Then
name = Cell.Value

'If the cell is not blank save location number
strCurFile = name & " " & month & ".xls"
'Concatenate the Excel extention on the end in order
'to create a file name of the form location#.xls

'Open the newly created file
Workbooks.Open Filename:=strCurFile, UpdateLinks:=0
Sheets("Contract Data").Visible = True

'Copy the revenue by segment data
Sheets("Contract Data").Select
Range("b12:b17").Select
Selection.Copy
Windows(conStrFile).Activate
'Initially select cell C11
Range(Cells(11, colval), Cells(11, colval)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy the revenue check figurea
Windows(strCurFile).Activate
Sheets("Contract Data").Select
Range("B20").Select
Selection.Copy
Windows(conStrFile).Activate
Range(Cells(19, colval), Cells(19, colval)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy the cost of revenue by segment data
Windows(strCurFile).Activate
Sheets("Contract Data").Select
Range("C12:C17").Select
Selection.Copy
Windows(conStrFile).Activate
'Initially select cell B5
Range(Cells(24, colval), Cells(24, colval)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy the cost of revenue check figurea
Windows(strCurFile).Activate
Sheets("Contract Data").Select
Range("C20").Select
Selection.Copy
Windows(conStrFile).Activate
Range(Cells(32, colval), Cells(32, colval)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy the backlog by segment data
Windows(strCurFile).Activate
Sheets("Contract Data").Select
Range("G12:G17").Select
Selection.Copy
Windows(conStrFile).Activate
'Initially select cell B5
Range(Cells(51, colval), Cells(51, colval)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Copy the backlog gross profit by segment data
Windows(strCurFile).Activate
Sheets("Contract Data").Select
Range("H12:H17").Select
Selection.Copy
Windows(conStrFile).Activate
'Initially select cell B5
Range(Cells(61, colval), Cells(61, colval)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Windows(strCurFile).Activate
Application.CutCopyMode = False
ActiveWorkbook.Close saveChanges:=False
End If

colval = colval + 1

Next
'Save target workbook
ActiveWorkbook.Save
End Sub

 

[erik.van.geit]

Hi, ednance,
WELCOME to the Board !
it would help if you told us where the error is

let me guess
one of these lines ?
Sheets("Summary").Select
Sheets("Contract Data").Select
does those sheets really exist ?

kind regards,
Erik

 

[Tazguy37]

As far as I know, you need to save this workbook:

conStrFile = conStrFile & ".xls"

before you can .Activate it.

By the way, you really don't need to .Activate it, if you specify its name in a Workbook Object when you do the .PasteSpecial that follows.

You could do this, instead:

    Workbooks(conStrFile).Worksheets(1).Cells(51, colVal).PasteSpecial xlValues

You didn't specify a worksheet, so I used the first worksheet tab. Also, all of the parameters except xlPasteType (xlValues) are the default values (you can find these in the VBA help fle), so I took those parameters out.

Hope that helps! Post back with any more questions about what I posted.

 

[erik.van.geit]

Taz,
your eyes are better then mine
you saw the line in BOLD
nice you jumped in !!

 

[ednance]

Its here:
'Copy the revenue by segment data
Sheets("Contract Data").Select
Range("b12:b17").Select
Selection.Copy
Windows(conStrFile).Activate

 

[erik.van.geit]

ednance,
wasn't it only on the bold line ?
please provide some clear feedback
did you look at Taz's post (andmine perhaps ??)

 

[ednance]

Re:

i had my file save as a different name than defined in the code. I changed the file name and now it works. Here was my problem,
Range("A5").Select
month = ActiveCell.Value
Range("a1").Select
conStrFile = ActiveCell.Value
conStrFile = conStrFile & ".xls"

A5= September 05 and A1= Segment Details, so it was looking for workbook named Segment Details September 05, and I had saved my file as S-1 Segment Details September 05. How simple, but hard to figure out as usual. Anyway, thank you for all your help.

 

[erik.van.geit]

FINE!
and now you can try to clean it up
instead of

Range("A5").Select 
month = ActiveCell.Value 
Range("a1").Select 
conStrFile = ActiveCell.Value 
conStrFile = conStrFile & ".xls"

you can code something along those lines

month = Range("A5").Value 
conStrFile = Range("a1").Value & ".xls"

best regards,
Erik