Posts by legin52
-
-
OK first step is to create a table of weekend dates (and or Holidays).
somewhere possibly on a new sheet which can be hidden later if required, in cell A1 (for example) Type in the date 01/01/2018 in B1 type in the day on which your chosen date fell in 2018 it was a Monday then by highlighting the 2 cells and grabbing the autofill cross and dragging down to a date you want, say 31/12/2018. This will give you a calendar. now apply a filter for Monday through Friday to be visible then delete them. This will leave you a list of all the dates for all the Saturdays and Sundays through the period.
At this point I would make use of the name manager to use in the later formula.
This has the added benefit of you being able to add specific bank holidays such as Easter and Christmas (by doing it manually if required.
Now I am going to send the sheet I created so you can see the formula and my construction might be of help.Hope it helps
Regards
legin52 [ATTACH]n1204944[/ATTACH] -
Ooops! my mistake. :0ops:
Quite new to Forum activity anywhere.
Will compose my possible solution and post shortly.
Regards
legin52 -
I know this was 2 years old but if you haven't got a solution and you would still like one then I have one for you. Let me know.
Regards -
Hello everyone.
I need help to reduce the chances of me having to do a comb over to hide the increasing self created bald patch.I have a spread sheet (attached example of) that takes data from different sources and actions it in a cell
I need it to look neat for presentation purposes so blank cells where possible.
In the attached SS I have typed the steps required in order so all the info I have is contained within, I hope,
I trust one of you good people will be willing and able to help
Thanks Legin 52 [ATTACH]n1204894[/ATTACH]
-
Hello.
I am hoping somebody can help me as my knowledge and logic have been exhausted.
Problem:
I have a workbook with 2 sheets in it.
‘Printer Macro’ from here known as ‘1’
‘Printer Header Sheet’ from here known as ‘2’
A set of data is imported to ‘1’. It occupies columns A to L
However the number of rows occupied changes each time the project is run and it is this that is causing me problems.
What I am trying to achieve is:
Step1: Select A:L from row 1. Copy the data. Go to ‘2’ and paste (values and format) into cell A2 of this sheet. Then print sheet ‘2’.
Step 2: Repeat Step 1 but with Row 2 (A:L from ‘1’). Then paste into the same cell as step 1 (i.e. A2 of sheet ‘2’). Then print sheet ‘2’
This repeats for every row in sheet ‘1’ with data in it.
How can I get it to repeat the actions for all lines with data, one at a time, but stop if there is no data on a row (this being the end of the Macro?.
Suggestions will be gratefully appreciated -
Re: Adding time to create cell with 2 hyphenated times in it
Oh! my ***!
Hours, trying everything I knew or thought might work.
Obviously I don't know enough.
KjBox does.
My Grandchildren will thank you in the future as I do now.
From one VERY pleased member.
-
Re: Adding time to create cell with 2 hyphenated times in it
TGry that again..
[ATTACH=CONFIG]73670[/ATTACH]
-
-
Hello people of talent and knowledge. I hope one of you can help.
I have a sheet, into column N of which, I will be pasting a ‘time’. As pasting continues down the column, the time will vary, increasing or decreasing but only by the full hour. It will be formatted as “09:00” for example.
I would like each cell in column M to take the time in the related cell in column N (i.e. M2 to ‘see’ N2) return it, add 1 hour to it and return the time formatted as “09:00 – 10:00”.
-
Re: Formatting of dates in user forms
Yeesssss!
Double thanks
Worked a treat.
eternally grateful
Regards
-
Re: Formatting of dates in user forms
Thanks for the speedy response.
I did as you suggested (unless Mr stupid here has misinterpreted your instruction)1. Deleted the following:
CodePrivate Sub UserForm_Initialize() txtDate.Text = Format(Now(), "dd/mm/yyyy") End Sub
2. The changed:Codews.Cells(newRow, 2).Value = Me.txtDate.ValueWith
Codews.Cells(newRow, 2).Value = Date
I can enter the date in the user form as I want but on the click to enter the data onto the spreadsheet today's date is entered
[ATTACH=CONFIG]66440[/ATTACH]
It can only be me, please tell me where I've gone wrong.
Regards -
Re: Conditional Formatting Help
Scratch bit about leaving it at work
Just found a copy that I was working on prior to the finished articleSee attached
Investigate the clock and the macro to create it
look at the conditional formatting (not forgetting you can copy and paste formatting (including 'conditional')Hope that helps
Regards -
Re: Conditional Formatting Help
Hi,
I did this myself a few weeks ago but my problem was to change the cell colour based upon a time rather than a date. The solution I found was to install a clock on the spreadsheet. It can be formatted to include the date as well as the time. Then you set your deadline and when it goes past that the relevant formatting kicks in.
I suggest you put the clock on a separate sheet then hide it but ensuring the clock applies to the workbook.I could send you a copy of mine but it is at work and I am now on a weeks holiday, but if you want....
-
Hello
So, I have created a user form to enter data onto a spreadsheet. The problem is....(Please see code below)when the form opens it automatically puts today's date in the "txtDate" box and when the enter key is pressed it loads the date in the desired format onto the spreadsheet. The desired format is dd/mm/yyyy.If I manually change the date in the user form it reverts back and enters on the sheet as mm/dd/yyyy
However I don't want to use the "Today()" what I have been trying (many, many ways) is to leave the date box clear on the user form and manually enter the date (dd/mm/yyyy) in the user form and to, when the enter button is pressed to have it transferred to the spreadsheet in the same format.
Code
Display MorePrivate Sub btnCancel_Click() Unload Me End Sub Private Sub btnInput_Click() Dim ws As Worksheet Set ws = Worksheets("Appointments") Dim newRow As Long newRow = Application.WorksheetFunction.CountA(ws.Range("B:B")) + 1 ws.Cells(newRow, 2).Value = Me.txtDate.Value ws.Cells(newRow, 3).Value = Me.txtTime.Value ws.Cells(newRow, 4).Value = Me.txtLocation.Value ws.Cells(newRow, 5).Value = Me.txtDepartment.Value End Sub Private Sub UserForm_Initialize() txtDate.Text = Format(Now(), "dd/mm/yyyy") End SubIt is the last three lines that is giving me a hair pulling problem (I am now bald).
To learn should be desired, to help is inspiring.
Please 'inspire' me and quench a little of my 'desire'.
Legin52 -
Re: Macro must read changing cell content
OK Thanks for the pointer and here is the workbook in question[ATTACH=CONFIG]60630[/ATTACH]
-
Firstly I am something of a novice at Macros – I have to use the recorder to create them as writing VBA is a bit of a mystery. Please bear this in mind if you choose to reply to my query.
My macro operates a series of steps which start by applying a filter to a data input sheet.
It takes a number from a cell on a different sheet and uses that in the filter.
My problem is that the ‘recorder’ has recorded the cell (E2) as the one to copy. It currently (at the time of recording the macro) contained “003”. The following week’s data means that ‘E2’ may contain a different number eg “010”. But when the macro runs it still ‘sees’ “003”.
It then goes onto read cell “F2” on the same sheet and has the same problem with the contents “003Wk17”
I need the macro to recognise the contents of the cell at the time the macro runs rather than staying with what was in the cell at the time of the recording the Macro.
I have included my coding to help
Any help will be gratefully received.Code
Display MoreSub Macro1() ' ' Macro1 Macro ' ' Range("E2").Select Selection.Copy Sheets("Report Data").Select ActiveSheet.Range("$A$1:$P$61").AutoFilter Field:=3, Criteria1:="=003", _ Operator:=xlAnd Application.CutCopyMode = False Selection.Copy Sheets.Add After:=ActiveSheet Sheets("Sheet1").Select ActiveSheet.Paste Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select Sheets("File Namer").Select Range("F2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select Sheets("Sheet1").Name = "003WK17" Sheets("003WK17").Select Application.CutCopyMode = False Sheets("003WK17").Move Sheets("003WK17").Select Sheets("003WK17").Name = "003WK17" ChDir "E:\A Files Today\New folder\Macro Problem" ActiveWorkbook.SaveAs Filename:= _ "E:\A Files Today\New folder\Macro Problem\003WK17.xlsx", FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close End Sub -
Re: Copy cell to accommodate cell variable content in VBA
Ger, sorry for not posting a thanks but I am the primary carer for my mother who has MND and it means that sometimes I don't get a lot of time.
Anyway thanks for this it works and does 'see' what ever value is in cell F2 and then runs the report, just fine.
However there is a secondary issue.
Once the above macro has selected the F2 data and run the report, it then sends the reported data to a new workbook. Here the sheet tab is renamed by using the contents of G2 on the same sheet as the F2 cell. Unfortunately your suggestion doesn't use the value in G2 it just uses the "006WK20" See line 15 in my original code (4 line section starting "Range("G2").Select". I tried adding to your code by changing "range("f2")" to "range("f2:g2") - but this did not work. I feel I am within touching distance but I am staggering to the finish line. Can you help?
With kind regards and hope
Legin -
Re: Copy cell to accommodate cell variable content in VBA
Hi sorry I'm a bit new to this. You are right the critical part of my mail is "I need the Macro.....Blah! blah!"
here is the first part of the code
Code
Display MoreRange("F2").Select Selection.Copy Sheets("Import Sheet").Select ActiveSheet.Range("$A$2:$Q$23").AutoFilter Field:=2, Criteria1:="=*006*", _ Operator:=xlAnd Range("A1").Select Selection.CurrentRegion.Select Sheets.Add After:=ActiveSheet Sheets("Import Sheet").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("File Namer").Select Range("G2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select Sheets("Sheet1").Name = "006WK20" Range("A1").Select Sheets("006WK20").Select Application.CutCopyMode = False Sheets("006WK20").Move Sheets("006WK20").Select Sheets("006WK20").Name = "006WK20" Range("A1").Select ChDir "J:\A Files Today\New folder" ActiveWorkbook.SaveAs Filename:="J:\A Files Today\New folder\006WK20.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close Sheets("File Namer").Select Range("F3").Select End SubFirst line selects a cell, whose data can change. Line 4 above shows the data in the cell which in this case is "006".
I need it to just copy the cell F2 whatever the data in the cell is.
Hope this explains. Maybe I should say I am a novice at writing the macro. I create macros by recording the task as I perform it. I can read and see the language in the macro but am not confident at writing it.
Thanks for your interest.
-
I have a list of some 50 different numbers. Weekly a single report is run in which only some of the names produce a report. That's the base. I can produce a list of the names that actually report data. I can create a macro that copies a cell (with a name in it) and run what I want it to do. I can do this for successive cells but it seems to record the cell data in the syntax rather than just the cell. I need the macro to see the cell and copy what ever is is the cell (which changes week to week). Is this possible?
