Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimising VBA Code
Good morning,
I hope someone can help me with this. I am busy designing a workbook that does the following: Each client account is listed in a column on my first worksheet, with the total quantity they are purchasing, eg: B C 8 Account Number Quantity 9 10001 10 10 H0002 10 11 AB003 10 12 B0005 10 Also, in cell C4 I have the purchase order reciept number, and in C5 I have the date. Each Account has its own worksheet within the workbook and I would like a macro that copies the quantity from worksheet 1 and pastes it into the correct client account worksheet. I have used the following code, however it only copies and pastes the information for the first account (there are going to be 80+!!), is there a way I can loop the code so it does the procedure for every account listed in the first worksheets columns? Code I am using (with comments): 'Copy quantity bought by client 10001 Range("D9").Select Selection.Copy 'Paste quantity bought by client 10001 to B19 Range("B19").Select ActiveSheet.Paste 'Copy reciept reference number Range("C4").Select Application.CutCopyMode = False Selection.Copy 'Paste receipt reference number to C19 Range("C19").Select ActiveSheet.Paste 'Copy Date reference Range("C5").Select Application.CutCopyMode = False Selection.Copy 'Past Date reference to A19 Range("A19").Select ActiveSheet.Paste 'Copy format needed for client accout - Date(A19) Quantity(B19) Reciept(C19) With Sheets("10001") lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 Rows("19").Copy .Rows(lr) End With Really would appreciate your help. If there is any confusion about what I am asking, please ask and I will try to clarify. Thank you Regards, PVANS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimising VBA Code
I assume you are using Row19 as a temporary one to build the row...If so you
dont need to do that...The below code loops through each account (starting from row9; you can adjust this) and update the values into the respective sheets. until a blank account number is found in column B..Make sure the sheet names are exactly same as mentioned in the first worksheet (even spaces count).. Run the below macro keeping the 1st sheet activated.. Try and feedback.. Sub Macro() Dim lngRow as Long, lr as Long lngRow = 9 Do While Range("B" & lngRow) < "" With Sheets(CStr(Range("B" & lngRow))) lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 ..Range("A" & lr) = Range("D" & lngRow) ..Range("B" & lr) = Range("C4") ..Range("C" & lr) = Range("C5") End With lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning, I hope someone can help me with this. I am busy designing a workbook that does the following: Each client account is listed in a column on my first worksheet, with the total quantity they are purchasing, eg: B C 8 Account Number Quantity 9 10001 10 10 H0002 10 11 AB003 10 12 B0005 10 Also, in cell C4 I have the purchase order reciept number, and in C5 I have the date. Each Account has its own worksheet within the workbook and I would like a macro that copies the quantity from worksheet 1 and pastes it into the correct client account worksheet. I have used the following code, however it only copies and pastes the information for the first account (there are going to be 80+!!), is there a way I can loop the code so it does the procedure for every account listed in the first worksheets columns? Code I am using (with comments): 'Copy quantity bought by client 10001 Range("D9").Select Selection.Copy 'Paste quantity bought by client 10001 to B19 Range("B19").Select ActiveSheet.Paste 'Copy reciept reference number Range("C4").Select Application.CutCopyMode = False Selection.Copy 'Paste receipt reference number to C19 Range("C19").Select ActiveSheet.Paste 'Copy Date reference Range("C5").Select Application.CutCopyMode = False Selection.Copy 'Past Date reference to A19 Range("A19").Select ActiveSheet.Paste 'Copy format needed for client accout - Date(A19) Quantity(B19) Reciept(C19) With Sheets("10001") lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 Rows("19").Copy .Rows(lr) End With Really would appreciate your help. If there is any confusion about what I am asking, please ask and I will try to clarify. Thank you Regards, PVANS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimising VBA Code
Hi Jacob,
Thank you so much for the code, it is almost working perfectly. It manages to update the client accounts just as I needed (as well as avoiding the temporary row 19, so thank you :) ). However, once the client accounts are updated, it shows the following error: Run-time error 9 Subscript out of range When i click debug, it highlights the following code: With Sheets(CStr(Range("B" & lngRow))) Could you please advise, thanks so much for the help thus far. Regards, PVANS "Jacob Skaria" wrote: I assume you are using Row19 as a temporary one to build the row...If so you dont need to do that...The below code loops through each account (starting from row9; you can adjust this) and update the values into the respective sheets. until a blank account number is found in column B..Make sure the sheet names are exactly same as mentioned in the first worksheet (even spaces count).. Run the below macro keeping the 1st sheet activated.. Try and feedback.. Sub Macro() Dim lngRow as Long, lr as Long lngRow = 9 Do While Range("B" & lngRow) < "" With Sheets(CStr(Range("B" & lngRow))) lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 .Range("A" & lr) = Range("D" & lngRow) .Range("B" & lr) = Range("C4") .Range("C" & lr) = Range("C5") End With lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning, I hope someone can help me with this. I am busy designing a workbook that does the following: Each client account is listed in a column on my first worksheet, with the total quantity they are purchasing, eg: B C 8 Account Number Quantity 9 10001 10 10 H0002 10 11 AB003 10 12 B0005 10 Also, in cell C4 I have the purchase order reciept number, and in C5 I have the date. Each Account has its own worksheet within the workbook and I would like a macro that copies the quantity from worksheet 1 and pastes it into the correct client account worksheet. I have used the following code, however it only copies and pastes the information for the first account (there are going to be 80+!!), is there a way I can loop the code so it does the procedure for every account listed in the first worksheets columns? Code I am using (with comments): 'Copy quantity bought by client 10001 Range("D9").Select Selection.Copy 'Paste quantity bought by client 10001 to B19 Range("B19").Select ActiveSheet.Paste 'Copy reciept reference number Range("C4").Select Application.CutCopyMode = False Selection.Copy 'Paste receipt reference number to C19 Range("C19").Select ActiveSheet.Paste 'Copy Date reference Range("C5").Select Application.CutCopyMode = False Selection.Copy 'Past Date reference to A19 Range("A19").Select ActiveSheet.Paste 'Copy format needed for client accout - Date(A19) Quantity(B19) Reciept(C19) With Sheets("10001") lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 Rows("19").Copy .Rows(lr) End With Really would appreciate your help. If there is any confusion about what I am asking, please ask and I will try to clarify. Thank you Regards, PVANS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimising VBA Code
Hi Jacob,
I fixed the error. I had not included the final total worksheet that was also in the D column. As a result, it is running perfectly. Thank you so much. You have really been very helpful. Have a good day. Regards, PVANS "PVANS" wrote: Hi Jacob, Thank you so much for the code, it is almost working perfectly. It manages to update the client accounts just as I needed (as well as avoiding the temporary row 19, so thank you :) ). However, once the client accounts are updated, it shows the following error: Run-time error 9 Subscript out of range When i click debug, it highlights the following code: With Sheets(CStr(Range("B" & lngRow))) Could you please advise, thanks so much for the help thus far. Regards, PVANS "Jacob Skaria" wrote: I assume you are using Row19 as a temporary one to build the row...If so you dont need to do that...The below code loops through each account (starting from row9; you can adjust this) and update the values into the respective sheets. until a blank account number is found in column B..Make sure the sheet names are exactly same as mentioned in the first worksheet (even spaces count).. Run the below macro keeping the 1st sheet activated.. Try and feedback.. Sub Macro() Dim lngRow as Long, lr as Long lngRow = 9 Do While Range("B" & lngRow) < "" With Sheets(CStr(Range("B" & lngRow))) lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 .Range("A" & lr) = Range("D" & lngRow) .Range("B" & lr) = Range("C4") .Range("C" & lr) = Range("C5") End With lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning, I hope someone can help me with this. I am busy designing a workbook that does the following: Each client account is listed in a column on my first worksheet, with the total quantity they are purchasing, eg: B C 8 Account Number Quantity 9 10001 10 10 H0002 10 11 AB003 10 12 B0005 10 Also, in cell C4 I have the purchase order reciept number, and in C5 I have the date. Each Account has its own worksheet within the workbook and I would like a macro that copies the quantity from worksheet 1 and pastes it into the correct client account worksheet. I have used the following code, however it only copies and pastes the information for the first account (there are going to be 80+!!), is there a way I can loop the code so it does the procedure for every account listed in the first worksheets columns? Code I am using (with comments): 'Copy quantity bought by client 10001 Range("D9").Select Selection.Copy 'Paste quantity bought by client 10001 to B19 Range("B19").Select ActiveSheet.Paste 'Copy reciept reference number Range("C4").Select Application.CutCopyMode = False Selection.Copy 'Paste receipt reference number to C19 Range("C19").Select ActiveSheet.Paste 'Copy Date reference Range("C5").Select Application.CutCopyMode = False Selection.Copy 'Past Date reference to A19 Range("A19").Select ActiveSheet.Paste 'Copy format needed for client accout - Date(A19) Quantity(B19) Reciept(C19) With Sheets("10001") lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 Rows("19").Copy .Rows(lr) End With Really would appreciate your help. If there is any confusion about what I am asking, please ask and I will try to clarify. Thank you Regards, PVANS |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimising VBA Code
Thanks for your feedback and you are most welcome!
If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Hi Jacob, I fixed the error. I had not included the final total worksheet that was also in the D column. As a result, it is running perfectly. Thank you so much. You have really been very helpful. Have a good day. Regards, PVANS "PVANS" wrote: Hi Jacob, Thank you so much for the code, it is almost working perfectly. It manages to update the client accounts just as I needed (as well as avoiding the temporary row 19, so thank you :) ). However, once the client accounts are updated, it shows the following error: Run-time error 9 Subscript out of range When i click debug, it highlights the following code: With Sheets(CStr(Range("B" & lngRow))) Could you please advise, thanks so much for the help thus far. Regards, PVANS "Jacob Skaria" wrote: I assume you are using Row19 as a temporary one to build the row...If so you dont need to do that...The below code loops through each account (starting from row9; you can adjust this) and update the values into the respective sheets. until a blank account number is found in column B..Make sure the sheet names are exactly same as mentioned in the first worksheet (even spaces count).. Run the below macro keeping the 1st sheet activated.. Try and feedback.. Sub Macro() Dim lngRow as Long, lr as Long lngRow = 9 Do While Range("B" & lngRow) < "" With Sheets(CStr(Range("B" & lngRow))) lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 .Range("A" & lr) = Range("D" & lngRow) .Range("B" & lr) = Range("C4") .Range("C" & lr) = Range("C5") End With lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "PVANS" wrote: Good morning, I hope someone can help me with this. I am busy designing a workbook that does the following: Each client account is listed in a column on my first worksheet, with the total quantity they are purchasing, eg: B C 8 Account Number Quantity 9 10001 10 10 H0002 10 11 AB003 10 12 B0005 10 Also, in cell C4 I have the purchase order reciept number, and in C5 I have the date. Each Account has its own worksheet within the workbook and I would like a macro that copies the quantity from worksheet 1 and pastes it into the correct client account worksheet. I have used the following code, however it only copies and pastes the information for the first account (there are going to be 80+!!), is there a way I can loop the code so it does the procedure for every account listed in the first worksheets columns? Code I am using (with comments): 'Copy quantity bought by client 10001 Range("D9").Select Selection.Copy 'Paste quantity bought by client 10001 to B19 Range("B19").Select ActiveSheet.Paste 'Copy reciept reference number Range("C4").Select Application.CutCopyMode = False Selection.Copy 'Paste receipt reference number to C19 Range("C19").Select ActiveSheet.Paste 'Copy Date reference Range("C5").Select Application.CutCopyMode = False Selection.Copy 'Past Date reference to A19 Range("A19").Select ActiveSheet.Paste 'Copy format needed for client accout - Date(A19) Quantity(B19) Reciept(C19) With Sheets("10001") lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1 Rows("19").Copy .Rows(lr) End With Really would appreciate your help. If there is any confusion about what I am asking, please ask and I will try to clarify. Thank you Regards, PVANS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I modify my code to offset the defined range and repeat theprocedure instead of duplicating my code? | Excel Programming | |||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
Minimising the Ribbon in an Auto_Open macro | Excel Discussion (Misc queries) | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
Minimising Excel | Excel Programming |