Posts by teknovision

Lost Again - light please?

Hi there!

I'm lost again. Although in the same format, the macro is used on worksheets that vary in the amount of data they contain, this causes errors here:

Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C9770")
Range("C2:C9770").Select
Cells.Select
Range("A1:C15177").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"G1"), Unique:=True
Columns("G:G").Select

How can I get 'Selection.AutoFill Destination:=Range("C2:C9770")' to update C2:C9770 to whatever it is in the worksheet then, use that value for 'Range("C2:C9770").Select' and for 'Range("A1:C15177"). ...'?

Thank you for any help you can provide, many many thanks!!

Phil

Ooops another question on this?

Hi there!!

I have another question for u kind folks, at 'step' 15 I have the following:

Range("A1:C15177").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _"G1"), Unique:=True

How can I make so that the range is a selection of available cells with data in them, like:
Selection, Selection.End(xlDown)
Selection, Selection.End(xlToRight)

Many many thanks again for any help!!

Philippe

Thank You Thank You!!

That did the trick, thank you very much for all your help!!

Nope

Thanks but I'm afraid the same error appears

Code

Thanks guys added your bit of code and it seems to work, thanks!!

Here's the code I created, sorry if it's a bit lengthy, I've Added >>> where the yellow arrow points to in debug.

Application.DisplayAlerts = False
Sheets("Service Inventory Report").Select
Range("C:C,F:F").Select
Range("F1").Activate
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=CONCATENATE(A:A,"","",B:B)"
Columns("C:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C9770")
Range("C2:C9770").Select
Cells.Select
Range("A1:C15177").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"G1"), Unique:=True
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("G:H").Select
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("G:H").EntireColumn.AutoFit
Range("G:G,G1").Select
Selection.Font.Bold = False
Range("G1").Select
Selection.Font.Bold = True
Range("G28").Select
Columns("H:H").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("H:H").ColumnWidth = 10.29
ActiveWindow.LargeScroll Down:=-1
Range("H1").Select
ActiveCell.FormulaR1C1 = "USers"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("H1").Select
ActiveCell.FormulaR1C1 = "Users"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Columns("A:F").Select
Selection.Delete Shift:=xlToLeft
Range("D26").Select
Range("A:B").SpecialCells(xlCellTypeVisible).Copy
ActiveWindow.SelectedSheets.Delete
Sheets.Add
>>>the yellow arrow points to this line] ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1").Select
Range("A:B").SpecialCells(xlCellTypeVisible).Copy
ActiveWindow.SelectedSheets.Delete
Sheets.Add
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SelectedSheets.Delete
Sheets("Users Variance").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Selection.Replace What:="Total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Count", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Grand", Replacement:="Total", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.DisplayAlerts = True

Hi all!!

I've created a macro which works great within worksheet now, I would like to clean up the trail of sheets it leaves behind i.e. three. So I thought, copy the data I need then ask it to delete the sheet before it creates a new one:

Range("A:B").SpecialCells(xlCellTypeVisible).Copy
ActiveWindow.SelectedSheets.Delete
Sheets.Add
ActiveSheet.Paste

It asks me whether I really want to Delet to which I click yes (is there a way for this pop-up not to appear?) then, I get a Run Time Error '1004', any ideas??

Many thanks,

Phil

Change History

Thanks Derk it was in fact the Change History set to 30 days and are related to this histopry.. . hope this helps somebody one day!!

Cheers,

Phil

Hi all!!

We're all sharing a spreadsheet and it seems to have grown from 4.5mb to 128mb and, in the networ folder where it is stored, a number of files have appeared i.e.1EDDC000, 67B27000 and BC40A100 of 7.4mb, 49.1mb and 69mb respectively!? Any ideas/suggestions would be very welcome??

Many thanks,

Phil

Hi all!

I have created a macro that results in a Grouped list. Now, I would like to copy and paste the 'headers' of each group without the 'detail' into another spreadsheet.

Everytime I select, copy the whole list it also pastes the 'detail' under each 'header'.. . Any help would be muchly appreciated?

Any help would be muchly appreciated.. Cheers,

Phil

Ps. Nice new forum btw!!

Excellent!!! Thank you very much for your help!!

Phil

Found the way to resize fonts in Custom Footer thanks!! Also, tried the macro, very sweet!! Thank you very much for your help folks!!

Hi all!

In Column A I have cells formatted for Dates and in Column B I have entered a formula =A2+12. the result of B2 is 12/01/2000.

I would like B2 result to 'appear' only when a date is entered in A2, how do I make B2 nothing until something in A2 is entered?

Any help would be muchly appreciated,

Many thanks,

Phil

Thanks, sorry for not getting back to you but, never got the email notification.. . anyway, thanks for your tip on using Page Setup but, I can't seem to change the font size in Custom Footer, it's way too big! Tried pasting in smaller fonts but, still appears way too big!?

I like the idea of having a macro that would place a cell at the bottom of the page how do I work that, bit green on the macro front?

Many many thanks,

Phil

Hi there!

I have some data I would like to print however, I would like to make sure that a cell at the bottom is printed as a footer like one would be able to do with Word, any ideas?

Many thanks,

Phil

Thanks/Cheers to you both!!!

Hi there!

I have a similar list in the first worksheet of my spreadsheet to your table example:
http://www.ozgrid.com/Excel/excel-vlookup-formula.htm

In the next (second) worksheet, I would like their age to appear automatically when their name is selected from a drop-down i.e. If I pull-down Kate in Cell range A1, cell B1 will get populated with 25.

I'm assuming the formula would have to sit in B1 but, I'm not sure at all what formula would do the trick.. . any help would be greatly appreciated?

Many many thanks,

Philippe

You rock (replace with favourite music genre), thank you very very much!!!!!

Hi there,

I am trying to add the total amount of time required to perform certain activities by person.

I have 12 activities per person and there are 8 persons, each of the cells, including the totals per person are formatted (Custom, hh:mm). I then add all of these cells to create a total time for all activities per person. In two cases the total hours show as 03:01 and 1:37. However, by looking at the number of hours per activity I calculated that there totals are actually 27h01m and 25h37m.

So, how do I get the total cells to display 27:01 and 25:37?

Any ideas would be very welcome!! Many thanks.. .

Phil

Thank you very very much!!

Hi there!

I would like to add years to a date i.e. day on which a contract has to be renewed

02/4/2004 + 2 (years) = date (??)

Any ideas would be welcome, thanks!!

Phil