Minimising VBA Code

[PVANS]

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

 

[Jacob Skaria]

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

 

[PVANS]

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

 

[PVANS]

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

 

[Jacob Skaria]

Thanks for your feedback and you are most welcome!

If this post helps click Yes