Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
I have a worksheet in Excel 2010 into which, each day, I paste two
columns from a temporary sheet into columns K and L. Values from K and L are then extracted into C2, D2, E2 and F2 using =IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$151),$L$8: $L$151,0))<"00:10",INDEX($K$8:$K$151,MATCH(MAX($L $8:$L$151),$L$8:$L$151,0)),"No output") =MAX($L$8:$L$151) {=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))} {=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0))} respectively. I then copy the contents of C2:F2 and paste them into C4:F4 and then, the next day, into C5:F5 and so on until the end of the month. The next month is on a separate sheet. I've struggled, and failed, to write a macro to automate this. Can anyone help? -- F www.vulcantothesky.org - 2015, the last year to see a Vulcan fly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
hi,
how have you nominated the tabs of each month? isabelle Le 2015-07-31 07:40, F a écrit : I have a worksheet in Excel 2010 into which, each day, I paste two columns from a temporary sheet into columns K and L. Values from K and L are then extracted into C2, D2, E2 and F2 using =IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$151),$L$8: $L$151,0))<"00:10",INDEX($K$8:$K$151,MATCH(MAX($L $8:$L$151),$L$8:$L$151,0)),"No output") =MAX($L$8:$L$151) {=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))} {=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0))} respectively. I then copy the contents of C2:F2 and paste them into C4:F4 and then, the next day, into C5:F5 and so on until the end of the month. The next month is on a separate sheet. I've struggled, and failed, to write a macro to automate this. Can anyone help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
does the tabs of each month must be created successively?
isabelle Le 2015-07-31 23:32, isabelle a écrit : hi, how have you nominated the tabs of each month? isabelle |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
i assumed that there is a "model" for the months sheet
http://www.cjoint.com/c/EHbeCbeGRma Sub test() oTab = "" & Year(Now) & "-" & Format(Month(Now), "00") oExists = WorksheetExists(oTab) If Not oExists Then Sheets("model").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = oTab LastRow = 4 Else LastRow = Sheets(oTab).Cells(Rows.Count, 3).End(xlUp).Row + 1 End If Sheets(oTab).Range(Cells(LastRow, 3), Cells(LastRow, 6)).Value = Sheets("paste two columns").Range("C2:F2").Value End Sub Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean On Error Resume Next WorksheetExists = (Sheets(WorksheetName).Name < "") Err.Clear End Function isabelle |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
Hi
The tab for each month is named after the month: January, February, March etc. -- F www.vulcantothesky.org - 2015, the last year to see a Vulcan fly On 01/08/2015 04:32, isabelle wrote: hi, how have you nominated the tabs of each month? isabelle Le 2015-07-31 07:40, F a écrit : I have a worksheet in Excel 2010 into which, each day, I paste two columns from a temporary sheet into columns K and L. Values from K and L are then extracted into C2, D2, E2 and F2 using =IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$151),$L$8: $L$151,0))<"00:10",INDEX($K$8:$K$151,MATCH(MAX($L $8:$L$151),$L$8:$L$151,0)),"No output") =MAX($L$8:$L$151) {=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))} {=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0))} respectively. I then copy the contents of C2:F2 and paste them into C4:F4 and then, the next day, into C5:F5 and so on until the end of the month. The next month is on a separate sheet. I've struggled, and failed, to write a macro to automate this. Can anyone help? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
Hi
No, each tab is already created. I treat them separately rather than as being 'connected'. -- F www.vulcantothesky.org - 2015, the last year to see a Vulcan fly On 01/08/2015 04:41, isabelle wrote: does the tabs of each month must be created successively? isabelle Le 2015-07-31 23:32, isabelle a écrit : hi, how have you nominated the tabs of each month? isabelle |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
Hi,
Am Fri, 31 Jul 2015 12:40:39 +0100 schrieb F: I've struggled, and failed, to write a macro to automate this. Can anyone help? why don't you write the values into C:F? Then you don't have to copy & paste values. Paste the new data to K:L and then run the macro. Sub Test() Dim dest As Range With ActiveSheet Set dest = .Cells(Rows.Count, 3).End(xlUp)(2) dest = Evaluate("=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$ 151),$L$8:$L$151,0))" _ & "<""00:10"",INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$1 51),$L$8:$L$151,0)),""No output"")") dest.Offset(, 1) = Evaluate("=MAX($L$8:$L$151)") dest.Offset(, 2) = Evaluate("=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))") dest.Offset(, 3) = Evaluate("=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0) )") End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
On 01/08/2015 10:09, Claus Busch wrote:
Hi, Am Fri, 31 Jul 2015 12:40:39 +0100 schrieb F: I've struggled, and failed, to write a macro to automate this. Can anyone help? why don't you write the values into C:F? Then you don't have to copy & paste values. Paste the new data to K:L and then run the macro. Sub Test() Dim dest As Range With ActiveSheet Set dest = .Cells(Rows.Count, 3).End(xlUp)(2) dest = Evaluate("=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$ 151),$L$8:$L$151,0))" _ & "<""00:10"",INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$1 51),$L$8:$L$151,0)),""No output"")") dest.Offset(, 1) = Evaluate("=MAX($L$8:$L$151)") dest.Offset(, 2) = Evaluate("=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))") dest.Offset(, 3) = Evaluate("=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0) )") End With End Sub Regards Claus B. Hi Claus Many thanks for that. It works really well except I had not realised it was relevant to say that there is data in A36:E40 and so the macro is writing the values into C41:F41 and below. Is there a solution which ignores the presence of the data in A36:E40 and so start writing in C4:F4 (the first empty area above A36:E40) and then subsequent rows? Regards -- Frank |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
Hi Frank,
Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F: Is there a solution which ignores the presence of the data in A36:E40 and so start writing in C4:F4 (the first empty area above A36:E40) and then subsequent rows? then you have to hardcode the target cell every day. My suggestion: Delete C4:F40 first With ActiveSheet .Range("C4:F40").ClearContents Set dest = .Cells(Rows.Count, 3).End(xlUp)(2) dest = Evaluate("=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$ 151),$L$8:$L$151,0))" _ & "<""00:10"",INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$1 51),$L$8:$L$151,0)),""No output"")") dest.Offset(, 1) = Evaluate("=MAX($L$8:$L$151)") dest.Offset(, 2) = Evaluate("=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))") dest.Offset(, 3) = Evaluate("=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0) )") End With Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
Hi Frank,
Am Mon, 3 Aug 2015 12:07:47 +0200 schrieb Claus Busch: .Range("C4:F40").ClearContents ignore the previous post. You cannot delete every time you run the macro. Delete that range manually before first run. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
Hi again,
Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F: Is there a solution which ignores the presence of the data in A36:E40 and so start writing in C4:F4 (the first empty area above A36:E40) and then subsequent rows? if you run the macro every day you could create the row with the day: Set dest = .Cells(Day(Date) + 3, 3) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
On 03/08/2015 11:20, Claus Busch wrote:
Hi again, Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F: Is there a solution which ignores the presence of the data in A36:E40 and so start writing in C4:F4 (the first empty area above A36:E40) and then subsequent rows? if you run the macro every day you could create the row with the day: Set dest = .Cells(Day(Date) + 3, 3) Regards Claus B. Hi And thanks again. Trying hard to educate myself... would Set dest = Range("C36").End(xlUp)(2) do what I want? Regards -- Frank |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
Hi Frank,
Am Mon, 3 Aug 2015 12:01:17 +0100 schrieb F: Trying hard to educate myself... would Set dest = Range("C36").End(xlUp)(2) do what I want? not if you have data in C1:C40. Then it will find C41 Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to paste from clipboard
On Friday, July 31, 2015 at 6:42:05 AM UTC-5, F wrote:
I have a worksheet in Excel 2010 into which, each day, I paste two columns from a temporary sheet into columns K and L. Values from K and L are then extracted into C2, D2, E2 and F2 using =IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$151),$L$8: $L$151,0))<"00:10",INDEX($K$8:$K$151,MATCH(MAX($L $8:$L$151),$L$8:$L$151,0)),"No output") =MAX($L$8:$L$151) {=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))} {=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0))} respectively. I then copy the contents of C2:F2 and paste them into C4:F4 and then, the next day, into C5:F5 and so on until the end of the month. The next month is on a separate sheet. I've struggled, and failed, to write a macro to automate this. Can anyone help? -- F www.vulcantothesky.org - 2015, the last year to see a Vulcan fly I need help!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't paste from clipboard in my macro | Excel Discussion (Misc queries) | |||
Macro to Paste Picture in email body from Clipboard | Excel Programming | |||
Paste from clipboard only | Excel Worksheet Functions | |||
Transfer clipboard from Task pane clipboard(office?) content to Excel (windows?) clipboard? | Excel Programming | |||
Store clipboard before "vba do cut&paste" then recover stored information to clipboard? | Excel Programming |