Posts by p45cal

Re: Pivot Table - Hide Columns + Maintain Grand Total

cross posts:
http://www.vbaexpress.com/foru…Total&p=316800#post316800
http://www.mrexcel.com/forum/e…maintain-grand-total.html

Re: Compare two sentences and highlight in different color

also cross posted at
http://www.excelforum.com/exce…t-in-different-color.html
http://www.mrexcel.com/forum/e…ight-different-color.html

Re: Excel VBA Multiple Automatic Goal Seek

Only if you also post links to everywhere you have cross posted. This applies to (I think) all forums.
Some light reading: http://www.google.co.uk/url?sa…fNg&bvm=bv.69411363,d.ZWU

Re: Excel VBA Multiple Automatic Goal Seek

deleted (duplicate post)

Re: Excel VBA Multiple Automatic Goal Seek

cross posted at:
http://www.vbaexpress.com/forum/showthread.php?49943
http://www.excelguru.ca/forums…ired-%96-Please-Assist-us
http://www.msofficeforums.com/…c-goal-seek-required.html
http://www.mrexcel.com/forum/e…%96-please-assist-us.html
http://www.excelforum.com/exce…red-please-assist-us.html

and?…

Re: transfer data from Lotus macro to Excal VBA macro

Quote from michel5000;704979

sorry rollis13 I don't understand your message , your link sending me back to my thread that I posted today and which I am trying to find solution

michel5000, some light reading: http://www.excelguru.ca/conten…ge-to-forum-cross-posters
For others, also cross posted at:http://forum.chandoo.org/threa…to-excal-vba-macro.15222/

Re: Incorporating DropBox files within Excel

and http://chandoo.org/forum/threa…files-within-excel.12965/ where there are responses
http://www.excelguru.ca/forums…iles-from-Dropbox-Folders

Re: Compiling a list of observations into a table

Cross-posted here:
http://chandoo.org/forum/threa…-table-please-help.12720/
where I've made an offering; I wish I'd known of this thread before I put significant effort in. :censored:
(forum rule 4)

Re: Excel, VBA, Send Email

Cross posts:
http://www.ozgrid.com/forum/showthread.php?t=180888&p=675502
http://www.mrexcel.com/forum/e…lications-send-email.html

(again)

Re: VBA converting number string into number

try:

Range("B7") = Replace(Range("B7").Value, "-", "")

[COLOR="#0000CD"]MODERATOR EDIT
Please remember to use code tags, including with single-line code snippets.
Thanks.[/COLOR]

Re: VBA Help for Copy Paste Date Range (14 days previous from today)

Quote from p45cal;647839

Now I'll look into what's special about uploading to a sharepoint location..

To convert to a table:
DestSheet.ListObjects.Add(xlSrcRange, DestSheet.Range("A1").CurrentRegion, , xlYes).Name = "List1"
adjust name of course.

A bit of Googling implies that updating a Sharepoint list is easier using vba than using the GUI, however, methods used are very dependent on which versions of Excel/Sharepoint and even Access you may be using. I don't know much about Sharepoint!

Re: VBA Help for Copy Paste Date Range (14 days previous from today)

OK. For the copying; assuming you're copying to a blank sheet Sharepoint Upload, then try:

Set DestSheet = Sheets("Sharepoint Upload")
With Sheets("Actual Data")
  Set xx = .Range(.Cells(Application.Match(CLng(Date) - 14, .Range("B:B")), 1), .Cells(.Rows.Count, "B").End(xlUp))
  .Rows(1).Copy DestSheet.Rows(1)
End With
xx.Offset(1).Resize(xx.Rows.Count - 1).EntireRow.Copy DestSheet.Range("A2")

(untested)
Now I'll look into what's special about uploading to a sharepoint location..

Re: VBA Help for Copy Paste Date Range (14 days previous from today)

again,

Quote from p45cal;647818

with the Actual data tab active, do the two lines of code select the right data to copy?

Re: VBA Help for Copy Paste Date Range (14 days previous from today)

Yes, with the Actual data tab active, do the two lines of code select the right data to copy? Headers and stuff later (I'm not overly familiar with sherepoint but you've already given me a clue. (I put 14 in the code, it might be different) and you also say prior to today's date - so there is today's data rows there too; do you want to include today's data in the copying process?

Re: VBA Help for Copy Paste Date Range (14 days previous from today)

does:

Set xx = Range(Cells(Application.Match(CLng(Date) - 14, Range("B:B")), 1), Cells(Rows.Count, "B").End(xlUp))
xx.Offset(1).Resize(xx.Rows.Count - 1).EntireRow.Select

select the right rows to copy?

Re: Having issues with findnext function does not work like I want it.

Without looking too closely at your code, what sticks out like a sore thumb as being not terribly robust is the .findnext line which, according to Help:"Continues a search that was begun with the Find method". That'd be the last find method executed. You have three instances of .Find before that, two of them are conditional. That means that any one of the three could have been the last .Find.
Solution, be explicit with what you're looking for again with .Find instead of .FindNext.
Replace that .findnext line with the likes of:
Set Ind = .Find(PLCMSGString, after:= Ind, LookIn:=xlValues, lookat:=xlWhole)

Re: Delete Empty Rows

r = 12 'the first row that can be deleted
Range(Cells(r, 1), Cells(Rows.Count, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Re: Optimizing Copying Method

Sub blah()
Sheets("RST Pivot").Range("H6:I1000").Value = Sheets("RST").Range("G9:H1003").Value
End Sub

or if you won't know the size of the range

Sub blah2()
Set SourceRng = Sheets("RST").Range("G9:H1003")
Sheets("RST Pivot").Range("H6").Resize(SourceRng.Rows.Count, SourceRng.Columns.Count).Value = SourceRng.Value
End Sub

For the first macro, you don't need to enter formulae one cell at a time; the likes of

Range("H9:H1003").FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[1]=""""),WORKDAY(RC[-2],5,RC[-2]),"""")"

will do them all in one hit

Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row

right click on the Master sheet's tab and choose 'View Code'. When the vbe shows up paste this into it:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Cells.EntireRow.Hidden = False
End Sub

close the vbe and when you doubleclick the sheet all the rows will be unhidden.

..or just click the button and check all the checkboxes.

Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row

Yes, I saw the effect your changing the userform name did - corrected in the attached. There were also several minor errors, you had leg ulcers in twice on the sheet (each header has to be unique), you misspelt one of them in the code, all corrected.

Page breaks; instead of print preview I've moved to just leaving the sheet in page break preview mode when it's finished. I've abandoned putting all rows back to visible at the end - you don't need to see them at all anyway, and I moved the button so that it can never be hidden - it doesn't matter too much where it is since it's not printed.

So now, when you've chosen what you want printed and the macro does its stuff, working from top to bottom, you manually drag the page breaks which come in the middle of a table upwards, to line up with the top of that table. Then Print or Print Preview manually. Depending on how many of these you have to do, it may not be worth automating this.
You can leave the sheet as it is (you don't even have to savethe file).
Be patient, it's quite slow.
I'm not sure about the colours..