Code to auto fill Column A and B from Cells A2 and B2 down to last row.

[Bill Williamson]

Hello Everyone,

I am pulling a Customer Name and a CSO number from a workbook Name.
Populating them into Column A2 and B2
This part is working fine., What I need to do is have them fill down to last used row when the row qty varies between worksheets.


I am a newbie at this VBA so any advice you could give would be helpful.
This is the part of the macro I need help with.

[Code}
Dim CSO As String
Dim Customer As String
Dim lngstart As Long
Dim lngEnd As Long
Dim LeftEnd As Long
Dim RhtEnd As Long

'Find CSO# Starting and end Positions
' Which will be between the " " and the "."
'Pull CSO# out of file name

lngstart = InStr(ActiveWorkbook.Name, " ")
lngEnd = InStr(ActiveWorkbook.Name, ".")
CSO = Mid(ActiveWorkbook.Name, lngstart + 1, lngEnd - lngstart - 1)

'Pulls Customer Name out of File Name

LeftEnd = InStr(ActiveWorkbook.Name, "Left")
RhtEnd = InStr(ActiveWorkbook.Name, " ")
Customer = Mid(ActiveWorkbook.Name, Leftstart + 1, RhtEnd - Leftstart - 1)

'Populates A2 and B2 with Data

ActiveSheet.Range("A2") = CSO
ActiveSheet.Range("A1") = "CSO#"
ActiveSheet.Range("B1") = "Customer"
ActiveSheet.Range("B2") = Customer

[Code/]

Thanks in advance for any ideas.


Bill Williamson

 

[Fluff]

Because Leftstart was never given a value so it's treated as 0, so Customer = Mid(ActiveWorkbook.Name, Leftstart + 1, RhtEnd - Leftstart - 1) becomes
Customer = Mid(ActiveWorkbook.Name, 1, RhtEnd - 1) which is correct given the structure of your file names.

 

[Bill Williamson]

OK I get it, I was not too far off, thank you both for the help. Its is running great.

Now I am going to work on transferring it to another workbook.
If I cant get it figured out I will be back for a new thread

Thanks again both of you.

Regards,

Bill Williamson

 

[Fluff]

You're welcome & thanks for the feedback

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.