Run-time error '9':

ednance

New Member
Joined
Oct 26, 2005
Messages
6
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
As far as I know, you need to save this workbook:

Code:
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:

Code:
    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.
 

ednance

New Member
Joined
Oct 26, 2005
Messages
6
Its here:
'Copy the revenue by segment data
Sheets("Contract Data").Select
Range("b12:b17").Select
Selection.Copy
Windows(conStrFile).Activate
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
ednance,
wasn't it only on the bold line ?
please provide some clear feedback
did you look at Taz's post (andmine perhaps ??)
 

ednance

New Member
Joined
Oct 26, 2005
Messages
6
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
FINE!
and now you can try to clean it up
instead of
Code:
Range("A5").Select 
month = ActiveCell.Value 
Range("a1").Select 
conStrFile = ActiveCell.Value 
conStrFile = conStrFile & ".xls"
you can code something along those lines
Code:
month = Range("A5").Value 
conStrFile = Range("a1").Value & ".xls"
best regards,
Erik
 

Forum statistics

Threads
1,078,403
Messages
5,340,006
Members
399,348
Latest member
duel

Some videos you may like

This Week's Hot Topics

Top