Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Macro with a List
Hi Experts:
I would like to build a macro with the List for multiple individuals that will then enter the data from the List to the Summary Worksheet in the same Workbook. This is the example of what I want to do.... List Sheet John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% in Column D Put that data on the Summary Worksheet in Columns B, C, D & E I would also like to remove the % sign in Column D Can anyone help me with this? I would appreciate any assistance you can provide. I'm working on a new reporting document that is using VLOOKUPs....a lot of them every month....I think the Macro would be more efficient, but I need your help to build on. -- jeannie v |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Macro with a List
Why, Jeannie, fancy meeting you here! :-) It sounds to me as though you
already have an idea of how the program should work and something about it isn't doing what you think it should. Can you post some code and the error message, or whatever behavior you get that isn't what you want? I could say "just have your code look through the original report page and pull up the rows that have the name (or whatever) that you want, and then..." But doubtless you're already doing that, or trying. --- "jeannie v" wrote: Hi Bob: Thank you for the prompt response....I just inherited this report and it is HUGE...the report is filled with VLOOKUPs and I know I can make it more efficient with Macros..I"m taking the report is pieces...of which this is the first of many pieces to this report. It's not the same data every month or the same individuals (all would be in the List, though), so I was hoping that I could build a Macro that would pull and pop the correct data no matter who or what was identified for the month, each month. I had a Macro that I used a couple of years ago that did something similar, but I can't make it work for this report...there are differences that just don't work. --- "jeannie v" wrote: I would like to build a macro with the List for multiple individuals that will then enter the data from the List to the Summary Worksheet in the same Workbook. This is the example of what I want to do.... List Sheet John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% in Column D Put that data on the Summary Worksheet in Columns B, C, D & E I would also like to remove the % sign in Column D Can anyone help me with this? I would appreciate any assistance you can provide. I'm working on a new reporting document that is using VLOOKUPs....a lot of them every month....I think the Macro would be more efficient, but I need your help to build on. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Macro with a List
Hi Bob:
This is the Macro I used a couple of years ago....honestly, one of you, experts, gave this to me and it was much more advanced than I was able to build....Is there someway we could make it work for the example I documented below.....I got so lost when I tried to adapt this to what I wanted. This is more than what I need, I think...Can you help? Sub Summary Dim n As Integer, i As Integer, j As Integer, k As Integer, m As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet, wsl As Worksheet Dim mName As String, mSheet As String Set wsl = ThisWorkbook.Sheets("List") Set wss = ThisWorkbook.Sheets("Summary") wsl.Activate k = wsl.Cells(Rows.Count, "A").End(xlUp).Row If k = 0 Then Exit Sub For m = 1 To k mName = wsl.Cells(m, 1).Value: mSheet = wsl.Cells(m, 2).Value Set wsd = ThisWorkbook.Sheets(mSheet) n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = WorksheetFunction.CountA(Range("A3:A17")) + 4 wsd.Activate Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = mName Then wss.Range("A" & i & ":L" & i).Copy dest.Select Selection.PasteSpecial Paste:=xlPasteValues End If Next i -- jeannie v "Bob Bridges" wrote: Why, Jeannie, fancy meeting you here! :-) It sounds to me as though you already have an idea of how the program should work and something about it isn't doing what you think it should. Can you post some code and the error message, or whatever behavior you get that isn't what you want? I could say "just have your code look through the original report page and pull up the rows that have the name (or whatever) that you want, and then..." But doubtless you're already doing that, or trying. --- "jeannie v" wrote: Hi Bob: Thank you for the prompt response....I just inherited this report and it is HUGE...the report is filled with VLOOKUPs and I know I can make it more efficient with Macros..I"m taking the report is pieces...of which this is the first of many pieces to this report. It's not the same data every month or the same individuals (all would be in the List, though), so I was hoping that I could build a Macro that would pull and pop the correct data no matter who or what was identified for the month, each month. I had a Macro that I used a couple of years ago that did something similar, but I can't make it work for this report...there are differences that just don't work. --- "jeannie v" wrote: I would like to build a macro with the List for multiple individuals that will then enter the data from the List to the Summary Worksheet in the same Workbook. This is the example of what I want to do.... List Sheet John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% in Column D Put that data on the Summary Worksheet in Columns B, C, D & E I would also like to remove the % sign in Column D Can anyone help me with this? I would appreciate any assistance you can provide. I'm working on a new reporting document that is using VLOOKUPs....a lot of them every month....I think the Macro would be more efficient, but I need your help to build on. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Macro with a List
I'm no expert; I started VBA/Excel programming in earnest just a couple years
ago, though I fooled around with it back just before Y2K. I can figure out what this does - it doesn't look very complicated - but if you don't already write code yourself then I should think it a bad idea for you to try to adapt someone else's program. Let me put that another way: It's a GOOD idea for you to adapt someone else's code, but you should sit down with it and figure out what it does and how you want to change it, not just ask someone else to help - because until you've sweated over it yourself, you don't know the code and will very possibly not recognize when it starts lying to you. I don't mind a bit helping you to understand it, and coaching you in getting it to what you want it to do. But unless we go through that work ("all that work"), decades of experience tell me it won't do you much good. --- "jeannie v" wrote: This is the Macro I used a couple of years ago....honestly, one of you, experts, gave this to me and it was much more advanced than I was able to build....Is there someway we could make it work for the example I documented below.....I got so lost when I tried to adapt this to what I wanted. This is more than what I need, I think...Can you help? Dim n As Integer, i As Integer, j As Integer, k As Integer, m As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet, wsl As Worksheet Dim mName As String, mSheet As String Set wsl = ThisWorkbook.Sheets("List") Set wss = ThisWorkbook.Sheets("Summary") wsl.Activate k = wsl.Cells(Rows.Count, "A").End(xlUp).Row If k = 0 Then Exit Sub For m = 1 To k mName = wsl.Cells(m, 1).Value: mSheet = wsl.Cells(m, 2).Value Set wsd = ThisWorkbook.Sheets(mSheet) n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = WorksheetFunction.CountA(Range("A3:A17")) + 4 wsd.Activate Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = mName Then wss.Range("A" & i & ":L" & i).Copy dest.Select Selection.PasteSpecial Paste:=xlPasteValues End If Next i Next m --- "Bob Bridges" wrote: It sounds to me as though you already have an idea of how the program should work and something about it isn't doing what you think it should. Can you post some code and the error message, or whatever behavior you get that isn't what you want? I could say "just have your code look through the original report page and pull up the rows that have the name (or whatever) that you want, and then..." But doubtless you're already doing that, or trying. --- "jeannie v" wrote: I would like to build a macro with the List for multiple individuals that will then enter the data from the List to the Summary Worksheet in the same Workbook. This is the example of what I want to do.... List Sheet John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% in Column D Put that data on the Summary Worksheet in Columns B, C, D & E I would also like to remove the % sign in Column D Can anyone help me with this? I would appreciate any assistance you can provide. I'm working on a new reporting document that is using VLOOKUPs - a lot of them every month - I think the Macro would be more efficient, but I need your help to build on. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Macro with a List
Hi Bob:
Thank you for the offer to help me. I am working 14 -16 Hrs a day and just don't have time right now to work through this...I do appreciate that you are so willing to help me understand the Macro codes...When I have time, I will definitely call upon you for assistance...it's just not possible right now. I just wanted to uncomplicate this HUGE spreadsheet that I inherited. My inkling is that you are an EXPERT, though! -- jeannie v "Bob Bridges" wrote: I'm no expert; I started VBA/Excel programming in earnest just a couple years ago, though I fooled around with it back just before Y2K. I can figure out what this does - it doesn't look very complicated - but if you don't already write code yourself then I should think it a bad idea for you to try to adapt someone else's program. Let me put that another way: It's a GOOD idea for you to adapt someone else's code, but you should sit down with it and figure out what it does and how you want to change it, not just ask someone else to help - because until you've sweated over it yourself, you don't know the code and will very possibly not recognize when it starts lying to you. I don't mind a bit helping you to understand it, and coaching you in getting it to what you want it to do. But unless we go through that work ("all that work"), decades of experience tell me it won't do you much good. --- "jeannie v" wrote: This is the Macro I used a couple of years ago....honestly, one of you, experts, gave this to me and it was much more advanced than I was able to build....Is there someway we could make it work for the example I documented below.....I got so lost when I tried to adapt this to what I wanted. This is more than what I need, I think...Can you help? Dim n As Integer, i As Integer, j As Integer, k As Integer, m As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet, wsl As Worksheet Dim mName As String, mSheet As String Set wsl = ThisWorkbook.Sheets("List") Set wss = ThisWorkbook.Sheets("Summary") wsl.Activate k = wsl.Cells(Rows.Count, "A").End(xlUp).Row If k = 0 Then Exit Sub For m = 1 To k mName = wsl.Cells(m, 1).Value: mSheet = wsl.Cells(m, 2).Value Set wsd = ThisWorkbook.Sheets(mSheet) n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = WorksheetFunction.CountA(Range("A3:A17")) + 4 wsd.Activate Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = mName Then wss.Range("A" & i & ":L" & i).Copy dest.Select Selection.PasteSpecial Paste:=xlPasteValues End If Next i Next m --- "Bob Bridges" wrote: It sounds to me as though you already have an idea of how the program should work and something about it isn't doing what you think it should. Can you post some code and the error message, or whatever behavior you get that isn't what you want? I could say "just have your code look through the original report page and pull up the rows that have the name (or whatever) that you want, and then..." But doubtless you're already doing that, or trying. --- "jeannie v" wrote: I would like to build a macro with the List for multiple individuals that will then enter the data from the List to the Summary Worksheet in the same Workbook. This is the example of what I want to do.... List Sheet John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% in Column D Put that data on the Summary Worksheet in Columns B, C, D & E I would also like to remove the % sign in Column D Can anyone help me with this? I would appreciate any assistance you can provide. I'm working on a new reporting document that is using VLOOKUPs - a lot of them every month - I think the Macro would be more efficient, but I need your help to build on. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a Macro with a List
Well, let's do it this way, then: You have my email address, right? Send me
the below code and we'll go through it just one piece at a time. That way you can steal the time to improve your understanding of it - an investment in the future, and a not very distant future either - but only in small slices that won't affect the other things you have to do. In just a week or two you may see for yourself what you want to do with this, simply because you can see what this is doing. --- "jeannie v" wrote: Thank you for the offer to help me. I am working 14 -16 Hrs a day and just don't have time right now to work through this...I do appreciate that you are so willing to help me understand the Macro codes...When I have time, I will definitely call upon you for assistance...it's just not possible right now. I just wanted to uncomplicate this HUGE spreadsheet that I inherited. --- "Bob Bridges" wrote: I can figure out what this does - it doesn't look very complicated - but if you don't already write code yourself then I should think it a bad idea for you to try to adapt someone else's program. Let me put that another way: It's a GOOD idea for you to adapt someone else's code, but you should sit down with it and figure out what it does and how you want to change it, not just ask someone else to help - because until you've sweated over it yourself, you don't know the code and will very possibly not recognize when it starts lying to you. I don't mind a bit helping you to understand it, and coaching you in getting it to what you want it to do. But unless we go through that work ("all that work"), decades of experience tell me it won't do you much good. --- "jeannie v" wrote: This is the Macro I used a couple of years ago...one of you gave this to me and it was much more advanced than I was able to build. Is there some way we could make it work for the example I documented below? I got so lost when I tried to adapt this to what I wanted. This is more than what I need, I think. Dim n As Integer, i As Integer, j As Integer, k As Integer, m As Integer Dim rng As Range, dest As Range Dim wss As Worksheet, wsd As Worksheet, wsl As Worksheet Dim mName As String, mSheet As String Set wsl = ThisWorkbook.Sheets("List") Set wss = ThisWorkbook.Sheets("Summary") wsl.Activate k = wsl.Cells(Rows.Count, "A").End(xlUp).Row If k = 0 Then Exit Sub For m = 1 To k mName = wsl.Cells(m, 1).Value: mSheet = wsl.Cells(m, 2).Value Set wsd = ThisWorkbook.Sheets(mSheet) n = wss.Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To n j = WorksheetFunction.CountA(Range("A3:A17")) + 4 wsd.Activate Set dest = wsd.Range("A" & j & ":L" & j) If wss.Cells(i, "A").Value = mName Then wss.Range("A" & i & ":L" & i).Copy dest.Select Selection.PasteSpecial Paste:=xlPasteValues End If Next i Next m --- "Bob Bridges" wrote: It sounds to me as though you already have an idea of how the program should work and something about it isn't doing what you think it should. Can you post some code and the error message, or whatever behavior you get that isn't what you want? I could say "just have your code look through the original report page and pull up the rows that have the name (or whatever) that you want, and then..." But doubtless you're already doing that, or trying. --- "jeannie v" wrote: I would like to build a macro with the List for multiple individuals that will then enter the data from the List to the Summary Worksheet in the same Workbook. This is the example of what I want to do.... List Sheet: John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% in Column D. Put that data on the Summary Worksheet in Columns B, C, D & E. I would also like to remove the % sign in Column D I'm working on a new reporting document that is using VLOOKUPs - a lot of them every month - I think the Macro would be more efficient, but I need your help to build on. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create list with macro | Excel Discussion (Misc queries) | |||
Create a Macro with a List | Excel Worksheet Functions | |||
Create a macro with a list | Excel Worksheet Functions | |||
How to Create a macro from drop down list (Validation List) in excel | Excel Programming | |||
How to create a macro that compares a list to another list | New Users to Excel |