Hello,
I've created excel sheet contains macros and connected with SQL query.
I am getting below mentioned error.
"Run-time error '1004' Select Method of Worksheet Class failed Options "
Note: After recording the macro i've hide worksheet through VBA code
with password. Before hiding worksheet macros was run fine but after
hiding the worksheet it shows me above error.
I am getting error on line " sheets("Sheet2"). Select. (4th line)
Sheet2 which is hidden by VBA code after macros was recorded.
Below is whole query:
Sub Finaloutput()
'
' Finaloutput Macro
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("Sheet2").Select
Cells.Select
Selection.Copy
Sheets("Sheet6").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array
(1, 2, 3, 4, _
5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15), Replace:=True,
PageBreaks:=False, _
SummaryBelowData:=True
Selection.Copy
Sheets("Sheet8").Select
Range("J13").Select
ActiveWindow.SmallScroll Down:=-15
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Range("C8").Select
Cells.Replace What:=" Total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Cells.Select
Selection.Copy
Sheets("Sheet7").Select
ActiveWindow.SmallScroll Down:=-24
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K11").Select
Sheets("Sheet5").Select
Range("A7").Select
Sheets("Sheet4").Select
Range("AE5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A7").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=Range
("A7"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet5").Sort
.SetRange Range("A7:N162")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G10").Select
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlNotEqual, _
Formula1:="=0"
Selection.FormatConditions
(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("J15").Select
End Sub
I'm having trouble getting a distributed macro to work on a few
machines. The macro fails on the first line which reads:
Worksheet(1).Activate
I've also tried using the Sheets collection, but still the same error.
The computers in question run the following versions of excel:
Excel 2000 (9.0.6926 SP-3)
Excel 2000 (9.0.3821 SR-1)
Excel 2000 (9.0.2720)
I thought it might be a versioning issue (I developed the macro in Excel
2002), but one other instance of Excel 2000 (9.0.6926 SP-3) runs the
macro fine.
Does anyone know if something would block the Activate method in Excel?
The worksheet in question is actually a tab-delimited file that is
opened with excel.
Chris Bloom
I've spent all day writing code to copy a worksheet and manipulate data. I have just finish writing the code to get data from other sources. Now when I run the macros all together, the part of the code that copies the existing sheet fails. and I get the error code '1004'
Here is the code I'm using:
Code:
'create copy of PPG Daten and Populate with new test data
Sheets("PPG Daten").Copy After:=Sheets(9)
'Process each step individually for 54 steps only
NewPPGDatenStep1
NewPPGDatenStufennr1
NewPPGDatenStep2
NewPPGDatenStufennr2
NewPPGDatenStep3
NewPPGDatenStufennr3
NewPPGDatenStep4
NewPPGDatenStufennr4
NewPPGDatenStep5
NewPPGDatenStufennr5
NewPPGDatenStep6
NewPPGDatenStufennr6
NewPPGDatenStep7
NewPPGDatenStufennr7
NewPPGDatenStep8
NewPPGDatenStufennr8
NewPPGDatenStep9
NewPPGDatenStufennr9
NewPPGDatenStep10
NewPPGDatenStufennr10
NewPPGDatenStep11
NewPPGDatenStufennr11
NewPPGDatenStep12
NewPPGDatenStufennr12
NewPPGDatenStep13
NewPPGDatenStufennr13
NewPPGDatenStep14
NewPPGDatenStufennr14
NewPPGDatenStep15
NewPPGDatenStufennr15
NewPPGDatenStep16
NewPPGDatenStufennr16
NewPPGDatenStep17
NewPPGDatenStufennr17
NewPPGDatenStep18
NewPPGDatenStufennr18
NewPPGDatenStep19
NewPPGDatenStufennr19
NewPPGDatenStep20
NewPPGDatenStufennr20
NewPPGDatenStep21
NewPPGDatenStufennr21
NewPPGDatenStep22
NewPPGDatenStufennr22
NewPPGDatenStep23
NewPPGDatenStufennr23
NewPPGDatenStep24
NewPPGDatenStufennr24
NewPPGDatenStep25
NewPPGDatenStufennr25
NewPPGDatenStep26
NewPPGDatenStufennr26
NewPPGDatenStep27
NewPPGDatenStufennr27
NewPPGDatenStep28
NewPPGDatenStufennr28
NewPPGDatenStep29
NewPPGDatenStufennr29
NewPPGDatenStep30
NewPPGDatenStufennr30
NewPPGDatenStep31
NewPPGDatenStufennr31
NewPPGDatenStep32
NewPPGDatenStufennr32
NewPPGDatenStep33
NewPPGDatenStufennr33
NewPPGDatenStep34
NewPPGDatenStufennr34
NewPPGDatenStep35
NewPPGDatenStufennr35
NewPPGDatenStep36
NewPPGDatenStufennr36
NewPPGDatenStep37
NewPPGDatenStufennr37
NewPPGDatenStep38
NewPPGDatenStufennr38
NewPPGDatenStep39
NewPPGDatenStufennr39
NewPPGDatenStep40
NewPPGDatenStufennr40
NewPPGDatenStep41
NewPPGDatenStufennr41
NewPPGDatenStep42
NewPPGDatenStufennr42
NewPPGDatenStep43
NewPPGDatenStufennr43
NewPPGDatenStep44
NewPPGDatenStufennr44
NewPPGDatenStep45
NewPPGDatenStufennr45
NewPPGDatenStep46
NewPPGDatenStufennr46
NewPPGDatenStep47
NewPPGDatenStufennr47
NewPPGDatenStep48
NewPPGDatenStufennr48
NewPPGDatenStep49
NewPPGDatenStufennr49
NewPPGDatenStep50
NewPPGDatenStufennr50
NewPPGDatenStep51
NewPPGDatenStufennr51
NewPPGDatenStep52
NewPPGDatenStufennr52
NewPPGDatenStep53
NewPPGDatenStufennr53
NewPPGDatenStep54
NewPPGDatenStufennr54
NewPPGDatenOtherColumns
The individual macros (NewPPGDaten...) work fine on their own, its the first line: Sheets("PPG Daten").Copy After:=Sheets(9) thats giving me difficulty.
Any ideas?
Hi,
I have a 100+ sheet excel worksheet. In it I have multiple references to a number of different subjects. Can I write a function to change all references from one word to another.
For example I want all instances of 'Adapt' changed to 'TriSys'.
Thanks
How do you locate the cells which have an external reference? I have tried
using the links function, but this only shows the files that are referenced,
not the cells that relate to them.
Thanks,
Hi Folks
First time posting. I dabble with Excel and usually find an answer online but this one has me stumped. Here is the sheet:
http://www.hvlistings.com/NASCAR2010.XLS
I plan to enter races on the Races sheet. Each race takes up 2 columns (Driver and Points for that race). I then want to use INDEX and MATCH to get the points onto the TEAMS page. If you look at Row 7 of the TEAMS page, it works OK until I get to Race 14. This is because the columns are now AA, AB, etc and the CHAR function does not work. Is there a better, cleaner way to do this? I put scores for Jimmy Johnson all the way through the 36 races (each score corresponds with the race #), so it is easy to test a fix.
Thanks in advance!
I usually find an answer online but this one has me stumped. Here is the sheet:
http://www.hvlistings.com/NASCAR2010.XLS
I plan to enter races on the Races sheet. Each race takes up 2 columns (Driver and Points for that race). I then want to use INDEX and MATCH to get the points onto the TEAMS page. If you look at Row 7 of the TEAMS page, it works OK until I get to Race 14. This is because the columns are now AA, AB, etc and the CHAR function does not work. Is there a better, cleaner way to do this? I put scores for Jimmy Johnson all the way through the 36 races (each score corresponds with the race #), so it is easy to test a fix.
Thanks in advance!
Ok, So I made a table (on worksheet 'Load Chart') that has Crane Sizes going down the left (A Column), and in Row 1 I have radius of the pick, and cells B2:AJ14 are all the weights of the picks that correspond with the size of the crane and the distance(radius) the boom extends. With that said, I need to make some sort of generator on another worksheet that will allow me to input a certain weight of a pick, and the radius that I am picking, and have it spit out the size crane I should use. Now keep in mind the sizes vary and if the table doesnt have an exact match it would have to choose a value higher, as I am guaranteed to pick a crane that can handle a certain load. SO it should reference the column (radius), pick the nearest weight (that is same or greater than) and read the corresponding crane size in column A that matches the row that the size is in. I know its much but I hope someone can help me.
After modifying a few cell references, my Excel worksheet stopped
communicating within itself among other referenced tabs(worksheets). How do
I correct this without starting over.
In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.
Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.
I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.
Suggestions?
