| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macro to populate a list
I am new to macros but have worked out how to transfer the results of a
calculation to a row on another sheet. When I clear the original data to enter more how can I transfer the second lot of results to the next row down on the second sheet. I discovered a GoTo under Edit but cannot fathom if this is where I need to be. Many thanks Margs |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Macro to populate a list
Hi Margs
To find the first empty row in column A you can use this: EmptyCell=Range("A1").End(XlDown).Address BTW: Showing the code you have made so far, will enable us to give a more precise answer. Regards, Per "Margs" skrev i meddelelsen ... I am new to macros but have worked out how to transfer the results of a calculation to a row on another sheet. When I clear the original data to enter more how can I transfer the second lot of results to the next row down on the second sheet. I discovered a GoTo under Edit but cannot fathom if this is where I need to be. Many thanks Margs |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Macro to populate a list
Hi Per,
I'm not sure what you want to see (sorry if I am being stupid) but I have copied the macro. I press Ctrl-d and all the information is transferred to the other sheet but when I try to do it again with new information it over writes the original row. I am very grateful for your time. Margs TransferDetails Macro ' Macro recorded 30/05/2008 by M.Liddell ' ' Keyboard Shortcut: Ctrl+d ' Range("H3").Select Selection.Copy Sheets("Sheet2").Select Range("H2:K2").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("A5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select "Per Jessen" wrote: Hi Margs To find the first empty row in column A you can use this: EmptyCell=Range("A1").End(XlDown).Address BTW: Showing the code you have made so far, will enable us to give a more precise answer. Regards, Per "Margs" skrev i meddelelsen ... I am new to macros but have worked out how to transfer the results of a calculation to a row on another sheet. When I clear the original data to enter more how can I transfer the second lot of results to the next row down on the second sheet. I discovered a GoTo under Edit but cannot fathom if this is where I need to be. Many thanks Margs |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Macro to populate a list
Hi Margs
Now that I have seen your macro, I will use .Offset(Row, Column) to change row. See the code below: Sheets("Sheet1").Select Range("H3").Copy Sheets("Sheet2").Select If Range("H2").Value = "" Then off = 0 Else off = Range("H1").End(xlDown).Row - 1 End If Range("H2:K2").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Copy Sheets("Sheet2").Select Range("A5").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Regards, Per "Margs" skrev i meddelelsen ... Hi Per, I'm not sure what you want to see (sorry if I am being stupid) but I have copied the macro. I press Ctrl-d and all the information is transferred to the other sheet but when I try to do it again with new information it over writes the original row. I am very grateful for your time. Margs TransferDetails Macro ' Macro recorded 30/05/2008 by M.Liddell ' ' Keyboard Shortcut: Ctrl+d ' Range("H3").Select Selection.Copy Sheets("Sheet2").Select Range("H2:K2").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("A5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select "Per Jessen" wrote: Hi Margs To find the first empty row in column A you can use this: EmptyCell=Range("A1").End(XlDown).Address BTW: Showing the code you have made so far, will enable us to give a more precise answer. Regards, Per "Margs" skrev i meddelelsen ... I am new to macros but have worked out how to transfer the results of a calculation to a row on another sheet. When I clear the original data to enter more how can I transfer the second lot of results to the next row down on the second sheet. I discovered a GoTo under Edit but cannot fathom if this is where I need to be. Many thanks Margs |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Macro to populate a list
Thanks Per, I brought up the edit screen for the macro, deleted what I had
entered then copied and pasted yours. When I tried to run the macro it came up with an error message about a Compile Error and Syntax error. I've copied the edit page below and it had highlighted in blue the line starting Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False and in red a similar line further down. I really appreciate you helping me. I try to do things from books but it is difficult to search for something when you don't know the name of what you are searching for. From a grateful Margs Sub Clear() ' ' Clear Macro ' Clear Entry for next one ' ' Keyboard Shortcut: Ctrl+c ' Range("H5").Select Selection.ClearContents Range("F9:O12").Select Selection.ClearContents End Sub Sub TransferDetails() ' ' TransferDetails Macro ' Macro recorded 30/05/2008 by M.Liddell ' ' Keyboard Shortcut: Ctrl+d ' Sheets("Sheet1").Select Range("H3").Copy Sheets("Sheet2").Select If Range("H2").Value = "" Then off = 0 Else off = Range("H1").End(xlDown).Row - 1 End If Range("H2:K2").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Copy Sheets("Sheet2").Select Range("A5").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select False End Sub "Per Jessen" wrote: Hi Margs Now that I have seen your macro, I will use .Offset(Row, Column) to change row. See the code below: Sheets("Sheet1").Select Range("H3").Copy Sheets("Sheet2").Select If Range("H2").Value = "" Then off = 0 Else off = Range("H1").End(xlDown).Row - 1 End If Range("H2:K2").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Copy Sheets("Sheet2").Select Range("A5").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Regards, Per "Margs" skrev i meddelelsen ... Hi Per, I'm not sure what you want to see (sorry if I am being stupid) but I have copied the macro. I press Ctrl-d and all the information is transferred to the other sheet but when I try to do it again with new information it over writes the original row. I am very grateful for your time. Margs TransferDetails Macro ' Macro recorded 30/05/2008 by M.Liddell ' ' Keyboard Shortcut: Ctrl+d ' Range("H3").Select Selection.Copy Sheets("Sheet2").Select Range("H2:K2").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("A5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select "Per Jessen" wrote: Hi Margs To find the first empty row in column A you can use this: EmptyCell=Range("A1").End(XlDown).Address BTW: Showing the code you have made so far, will enable us to give a more precise answer. Regards, Per "Margs" skrev i meddelelsen ... I am new to macros but have worked out how to transfer the results of a calculation to a row on another sheet. When I clear the original data to enter more how can I transfer the second lot of results to the next row down on the second sheet. I discovered a GoTo under Edit but cannot fathom if this is where I need to be. Many thanks Margs |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Macro to populate a list
Hi
The problem is wordwrap in your newsreader. Try this: Sub TransferDetails() ' ' TransferDetails Macro ' Macro recorded 30/05/2008 by M.Liddell ' ' Keyboard Shortcut: Ctrl+d ' Sheets("Sheet1").Select Range("H3").Copy Sheets("Sheet2").Select If Range("H2").Value = "" Then off = 0 Else off = Range("H1").End(xlDown).Row - 1 End If Range("H2:K2").Offset(off, 0).PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Copy Sheets("Sheet2").Select Range("A5").Offset(off, 0).PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select 'False End Sub regards, Per "Margs" skrev i meddelelsen ... Thanks Per, I brought up the edit screen for the macro, deleted what I had entered then copied and pasted yours. When I tried to run the macro it came up with an error message about a Compile Error and Syntax error. I've copied the edit page below and it had highlighted in blue the line starting Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False and in red a similar line further down. I really appreciate you helping me. I try to do things from books but it is difficult to search for something when you don't know the name of what you are searching for. From a grateful Margs Sub Clear() ' ' Clear Macro ' Clear Entry for next one ' ' Keyboard Shortcut: Ctrl+c ' Range("H5").Select Selection.ClearContents Range("F9:O12").Select Selection.ClearContents End Sub Sub TransferDetails() ' ' TransferDetails Macro ' Macro recorded 30/05/2008 by M.Liddell ' ' Keyboard Shortcut: Ctrl+d ' Sheets("Sheet1").Select Range("H3").Copy Sheets("Sheet2").Select If Range("H2").Value = "" Then off = 0 Else off = Range("H1").End(xlDown).Row - 1 End If Range("H2:K2").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Copy Sheets("Sheet2").Select Range("A5").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select False End Sub "Per Jessen" wrote: Hi Margs Now that I have seen your macro, I will use .Offset(Row, Column) to change row. See the code below: Sheets("Sheet1").Select Range("H3").Copy Sheets("Sheet2").Select If Range("H2").Value = "" Then off = 0 Else off = Range("H1").End(xlDown).Row - 1 End If Range("H2:K2").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Copy Sheets("Sheet2").Select Range("A5").Offset(off, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Regards, Per "Margs" skrev i meddelelsen ... Hi Per, I'm not sure what you want to see (sorry if I am being stupid) but I have copied the macro. I press Ctrl-d and all the information is transferred to the other sheet but when I try to do it again with new information it over writes the original row. I am very grateful for your time. Margs TransferDetails Macro ' Macro recorded 30/05/2008 by M.Liddell ' ' Keyboard Shortcut: Ctrl+d ' Range("H3").Select Selection.Copy Sheets("Sheet2").Select Range("H2:K2").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("H5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Range("A5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("Sheet1").Select Range("F14:O14").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select "Per Jessen" wrote: Hi Margs To find the first empty row in column A you can use this: EmptyCell=Range("A1").End(XlDown).Address BTW: Showing the code you have made so far, will enable us to give a more precise answer. Regards, Per "Margs" skrev i meddelelsen ... I am new to macros but have worked out how to transfer the results of a calculation to a row on another sheet. When I clear the original data to enter more how can I transfer the second lot of results to the next row down on the second sheet. I discovered a GoTo under Edit but cannot fathom if this is where I need to be. Many thanks Margs |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Populate a Validation List | Excel Discussion (Misc queries) | |||
| How to populate part of a list | Excel Discussion (Misc queries) | |||
| Populate List Box | Excel Worksheet Functions | |||
| Populate List / Combo Box | Excel Discussion (Misc queries) | |||
| Populate Drop Down List | Excel Worksheet Functions | |||
Hybrid Mode
