Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and VB
Hiya
Already submitted a question but think I may have bn looking at the problem wrong, so gonna try a different way but I'm clueless when it comes to VB, once I have run a macro I want to edit it so that it goes to the available row (row header will be there tho) on a specific spreadsheet in stead of a certain cell or row, can I do this? If so how? Please remember I am a novice at VB so make it simple to follow please ;oD LOL Thanks Angi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and VB
If I have not understood you question, then ignore this response. Let's say
we have rows of data on Sheet 17 down to row #13 or any arbitrary row. We want to exit the macro with the selectied cell at the next available empty row, say in column A. So in our case the selection would be at A14. Sub hentzer() Sheets("Sheet 17").Activate Set r = Intersect(ActiveSheet.UsedRange, Range("A:A")) Cells(1, 1).Offset(r.Count, 0).Select End Sub -- Gary''s Student - gsnu200789 "Hentzer" wrote: Hiya Already submitted a question but think I may have bn looking at the problem wrong, so gonna try a different way but I'm clueless when it comes to VB, once I have run a macro I want to edit it so that it goes to the available row (row header will be there tho) on a specific spreadsheet in stead of a certain cell or row, can I do this? If so how? Please remember I am a novice at VB so make it simple to follow please ;oD LOL Thanks Angi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and VB
Gary
Thanks for your help, and this is closer than I have bn yet,! I may not have been very clear so I will try and explain LOL what I want to do is check a box and this set off a macro that goes to sheet 17, next available row say 15 Column A and pastes the info from sheet 1 into the relevant boxes and (I know how to do the pasting bit etc) but I dont know how to make it go to the next row and paste it there. I will have 2 check boxes 1 will go to external and the other to internal but from one list with sequential numbers, any ideas? ;oD Thanks for helping Angi "Gary''s Student" wrote: If I have not understood you question, then ignore this response. Let's say we have rows of data on Sheet 17 down to row #13 or any arbitrary row. We want to exit the macro with the selectied cell at the next available empty row, say in column A. So in our case the selection would be at A14. Sub hentzer() Sheets("Sheet 17").Activate Set r = Intersect(ActiveSheet.UsedRange, Range("A:A")) Cells(1, 1).Offset(r.Count, 0).Select End Sub -- Gary''s Student - gsnu200789 "Hentzer" wrote: Hiya Already submitted a question but think I may have bn looking at the problem wrong, so gonna try a different way but I'm clueless when it comes to VB, once I have run a macro I want to edit it so that it goes to the available row (row header will be there tho) on a specific spreadsheet in stead of a certain cell or row, can I do this? If so how? Please remember I am a novice at VB so make it simple to follow please ;oD LOL Thanks Angi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and VB
This would execute on the click event of Checkbox1. You can modify it to
suit your purposes. It finds the last row on sheet 17 using column A as the column to measure. If column A is not the appropriate column then change the 1 in the statement: Cells(Rows.Count, 1) to a number that represents the best column for finding the last row with data. Also the sheet name is assumed, based on GS's code. If that is different, then change that also. You would also need to specify a valid range to copy on the active sheet (or whichever sheet you are copying from). With all that fixed, the code would then copy from the source sheet to the destination sheet next available row. Sub Checkbox1_Click() RangeToCopy = 'Supplied by user lr17 = Sheets("Sheet17").Cells(Rows.Count, 1).End(xlUp).Row ActiveSheet.RangeToCopy.Copy Sheets("Sheet17").Range("A" & lr17 + 1) End Sub "Hentzer" wrote: Gary Thanks for your help, and this is closer than I have bn yet,! I may not have been very clear so I will try and explain LOL what I want to do is check a box and this set off a macro that goes to sheet 17, next available row say 15 Column A and pastes the info from sheet 1 into the relevant boxes and (I know how to do the pasting bit etc) but I dont know how to make it go to the next row and paste it there. I will have 2 check boxes 1 will go to external and the other to internal but from one list with sequential numbers, any ideas? ;oD Thanks for helping Angi "Gary''s Student" wrote: If I have not understood you question, then ignore this response. Let's say we have rows of data on Sheet 17 down to row #13 or any arbitrary row. We want to exit the macro with the selectied cell at the next available empty row, say in column A. So in our case the selection would be at A14. Sub hentzer() Sheets("Sheet 17").Activate Set r = Intersect(ActiveSheet.UsedRange, Range("A:A")) Cells(1, 1).Offset(r.Count, 0).Select End Sub -- Gary''s Student - gsnu200789 "Hentzer" wrote: Hiya Already submitted a question but think I may have bn looking at the problem wrong, so gonna try a different way but I'm clueless when it comes to VB, once I have run a macro I want to edit it so that it goes to the available row (row header will be there tho) on a specific spreadsheet in stead of a certain cell or row, can I do this? If so how? Please remember I am a novice at VB so make it simple to follow please ;oD LOL Thanks Angi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros in Personal.xls that would create two toolbars and buttonswith assigned macros | Excel Programming | |||
choose default macros Not Enabled / Macros Enable Setting | Excel Programming | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
Suppress the Disable Macros / Enable Macros Dialog | Excel Programming |