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

 

[DanteAmor]

This part is working fine., What I need to do is have them fill down to last used row

Nominally the last row used is based on a column.
In this case, columns A and B assume they are empty, so based on which column would we find the last row used?

 

[Bill Williamson]

Columns C-F will have data that goes to last used row. I wasn't sure if there was a short cut to fill down, or if I had to use Integers and ranges to fill them.

Thanks,

Bill Williamson

 

[Fluff]

How about

'Populates A2 and B2 with Data

Range("A1:B1") = Array("CSO#", "Customer")
Range("A2:B" & Range("C" & Rows.Count).End(xlUp).Row) = Array(CSO, Customer)

But on this line you are using variables that have no value

Customer = Mid(ActiveWorkbook.Name, Leftstart + 1, RhtEnd - Leftstart - 1)

 

[Bill Williamson]

That worked great, it also took 4 lines of code and shortened it to two. Thank you.
I had a piece of code I was trying to use similar to your wonderful solution, now I know why I couldn't get it to work,
I was missing The important word "Array".....So your solution also taught me a little.
As for the code you pointed out that is incorrect, That would be my lack of knowledge.
I tried to modify the code I used for the CSO ( which I found on internet ).
I'm more surprised that it somehow works...... Than I am that it is incorrect.
I would Humbly accept any corrections that you would offer.
Thanks

Bill

 

[Fluff]

What do your file names look like & what should be returned as CSO and Customer?

 

[Bill Williamson]

This is a Sample of a Workbook file name GeneralMills E341934012.xlsx
General Mils Being the customer name, always on the left with a space after it.
E341934012 is the CSO # It can actually start with several different letters. But
its always has a space before it and the "." after.

those are the references that I used for my code, I was just happy I had it working.

Bill

 

[DanteAmor]

Try this

Sub test()
  Dim s As String, CSO As String, Customer As String
  s = ActiveWorkbook.Name
  Customer = Left(s, InStr(1, s, " ") - 1)
  CSO = Mid(s, InStr(1, s, " ") + 1, Len(s) - Len(Customer) - 6)
  Range("A1:B1").Value = Array("CSO#", "Customer")
  [A2].Resize(Range("C" & Rows.Count).End(xlUp)(0).Row, 2).Value = Array(CSO, Customer)
End Sub

 

[Fluff]

Assuming you will not be using any old .xls files

With ActiveWorkbook
    Customer = Left(.Name, InStr(1, .Name, " ") - 1)
    CSO = Mid(.Name, InStr(1, .Name, " ") + 1, Len(.Name) - Len(Customer) - 6)
End With

 

[Bill Williamson]

Looking at how similar both of your codes are for this and how different and wrong mine was, I am still a little stumped how mine worked..

All files will be .xlsx