Posts by Barrie Davidson

That makes sense because it's the formula you are using references the same thing. What about changing your code to paste values? Something like:

Sub GetMyItem()
Sheets("Sheet1").Cells(27, 7).Formula = "=MyProgram|MyTopic!'MyItem'"
Sheets("Sheet1").Cells(27, 7).Copy
Sheets("Sheet1").Cells(27, 7).PasteSpecial xlPasteValues
End Sub
Sub GetMyItem2()
Sheets("Sheet1").Cells(29, 7).Formula = "=MyProgram|MyTopic!'MyItem'"
Sheets("Sheet1").Cells(29, 7).Copy
Sheets("Sheet1").Cells(29, 7).PasteSpecial xlPasteValues
End Sub

Quote

Originally posted by Pesky Weasel
But we never be anymore than that.... :cheers:

I'm with you on that one! :beergrin:

Quote

Originally posted by Pesky Weasel
Geez Barry! What sort of total idiot would do that?

ROFL!!!

MY KIND OF WEASEL!!!!! So our next step is to become drinking buddies? ;;)

Love this topic! :biggrin:

Here's mine, I received a request for a formula to count the number of commas in a cell. Went to the complete trouble of writing a UDF only to have someone else (Aladin Akyurek) point out:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

:o

Sometimes it's hard to see the forest for the trees. ;;)

And go easy on me fellas/gals when poking fun at me!!

Chris, best wishes in your business venture - I wish you all the success you can handle and maybe a little more than that.

If InStr(Range("A1"), ";") > 0 Then
    'it's found
Else
    'it's not found
End If

Regards,

Try something like this (of course I have no clue where you're putting the formula or where you're getting the sheet name so you'll have to change the code to meet your needs):

Dim SheetName As String
SheetName = ActiveSheet.Name
Sheets("Working").Range("A1").Formula = _
    "IF('" & SheetName & "'!D2="""","""",'" & _
    SheetName & "'!D2)"

Regards,

Have a look at

http://www.xcelfiles.com/Excel02.html#anchor_67

I think this is what you need.

Regards,

Could you use the indirect function? Something like:

=SUM(INDIRECT("R1!D1:G1"))

Regards,

I can't understand your requirements. Can you post an example?

Would this code work for you?

Dim SaveFileName As String
SaveFileName = "Date-" & Format(Now, "yyyymmdd") & _
    "-Time-" & Format(Now, "hhmmss") & ".xls"

Regards,

Will this work for you?

Quote

Originally posted by JJacob
Anybody know the code to protect a sheet against locked cell changes but allowing user changes to unlocked cells?

Sheets("Sheet1").Protect ("Password")

Quote

Originally posted by JJacob
Also Code for saving a specified sheet as a seperate workbook?

Sheets("Sheet1").Copy
ActiveSheet.SaveAs "YourFileName.xls"

Regards,

Quote

Originally posted by Imbuzi
Hi Ej,

I have had a look at your data and I would have to say that a pivot table is probably the quickest method of summing and analyzing your data.

If you have massive amounts of data you could use Access as an alternative.

HTH

Graeme

Display More

Agreed (know this isn't what you were looking for though, sorry )

Nothing was attached

Can you include the attachment?

Quote

Originally posted by Jas
That looks pretty slick... My VBA isn't strong enough for me to understand everything that's going on, so I'll spend some time with my nose in a book to figure it out! :wink2:

I'm particularly confused as to how this macro would know what files to open.

I also don't know what this line does: On Error GoTo 0

Other than that, I have at least partial clues...

Display More

Feel free to post any other questions you have on the code. I (or anyone else on this board :biggrin: ) will be more than happy to help you out.

Sincerely,

A1 is where the pivot table is (top left corner).

Does this help?

Try
[vba]
=GETPIVOTDATA(A1,"Grand Total")
[/vba]
where A1 is where the pivot table starts.

Regards,

Hi Jas, would this code work for you?

Sub CombineData()
' Written by Barrie Davidson
' Sep 11, 2003
Dim FilesToOpen
Dim DateToExtract As Date
Dim LastRow As Long
Dim CopyDest As Range




FilesToOpen = Application.GetOpenFilename
If IsArray(FilesToOpen) = False Then Exit Sub
Application.ScreenUpdating = False
DateRoutine:
On Error GoTo DateRoutine
DateToExtract = CDate(InputBox("Enter date to extract"))
On Error GoTo 0
For i = 1 To UBound(FilesToOpen)
    CopyDest = Workbooks("Summary.xls").Sheets("Sheet1") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    Workbooks.Open FilesToOpen(i)
    LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    ActiveSheet.Range("A1:D" & LastRow).AutoFilter _
        Field:=3, Criteria1:="=" & DateToExtract
    Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=CopyDest
    Application.DisplayAlerts = False
    ActiveWorkbook.Close False
    Application.DisplayAlerts = True
Next i
Application.ScreenUpdating = True


End Sub

This code assumes that your summary workbook is named "Summary.xls" and you are pasting the data in the worksheet named "Sheet1". Just make whatever changes you need.

Let me know if this meets your requirements.

Regards,

Try this

ActiveWorkbook.SaveAs FileName:=Format$(Range("PO_No").value ,"0000") & Range("Supplier_Code").Value & Range("Issue_date").Value

Hope this helps.

Regards,

You could try this code:

Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("H2:I" & LastRow).Formula = "=IF($A2=$A1,0,D2)"
Range("K2:K" & LastRow).Formula = "=IF($A2=$A1,0,G2)"
Range("J2:J" & LastRow).Formula = "=F2"
Range("H2:K" & LastRow).Copy
Range("D2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("H2:K" & LastRow).ClearContents
Range("A1").Select

Regards,