Macro Help needed ASAP

[titoexcel]

I have a template that needs to be populated for about 261 Vendors. Each template needs to be saved and submitted separately. I have a macro where I can go and press a short cut key and it populates the right fields but I am wondering if its possible to copy the template on to a new tab & populate it and keep going down the list of vendors?

Any help would be greatly appreciated.

Thanks in advance.

 

[titoexcel]

Michael,

Sorry about the confusion.

B12 I was referring to is o nthe template that needs to be populated.

If you want to reference the name from the list of vendors then it would be from a sheet called TOP 25. Do you want me provide you an example in like a private message or ?

 

[Michael M]

No....I just want to know where the list of vendors is....which column on TOP25 ???

 

[titoexcel]

No....I just want to know where the list of vendors is....which column on TOP25 ???

Column F

 

[Michael M]

Ok, If I understand you correctly, this will name each sheet with the Vendor Name and populate the values.
A couple of points to note....the values in each sheet will be just that, values, not formulas
Secondly, you say you have 260 od sheets, this will do as many as required, BUT, if each sheet has lots of formatting, etc, .....260+ sheets could cause issues with storage space, and could possibly slow, or even crash your machine !

Sub Copytotemplate()
Dim lr As Long, ws As Worksheet
Set ws = Sheets("TOP 25")
Application.ScreenUpdating = False
lr = Sheets("TOP 25").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("TOP 25").Columns("F:O").EntireColumn.Hidden = False
    For r = 2 To lr
        ws.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = ws.Range("F" & r)
        Range("B6").Value = ws.Range("A" & r)
        Range("B7").Formula = "=TODAY()"
        Range("B12").Value = ws.Range("F" & r)
        Range("B14").Value = ws.Range("J" & r)
        Range("B20").Value = ws.Range("F" & r)
        Range("B34").Value = ws.Range("Y" & r)
        Range("B35").Value = ws.Range("Z" & r)
        Range("B37").Value = ws.Range("AB" & r)
        Range("B38").Value = ws.Range("AC" & r)
        Range("B39").Value = ws.Range("AD" & r)
        Range("B40").Value = ws.Range("AE" & r)
        Range("B41").Value = ws.Range("AF" & r)
        Range("B42").Value = ws.Range("AG" & r)
        Range("B43").Value = ws.Range("AH" & r)
        Range("D12").Value = ws.Range("O" & r)
        Range("D13").Value = ws.Range("P" & r)
        Range("D15").Value = ws.Range("R" & r)
        Range("D16").Value = ws.Range("S" & r)
        Range("D17").Value = ws.Range("T" & r)
        Range("D18").Value = ws.Range("U" & r)
        Range("D20").Value = ws.Range("I" & r)
        Range("D21").Value = ws.Range("E" & r)
        Range("D22").Value = ws.Range("B" & r)
        Range("D28").Value = ws.Range("V" & r)
        Range("D29").Value = ws.Range("W" & r)
        Range("D30").Value = ws.Range("X" & r)
        Range("D34").Value = ws.Range("AI" & r)
        Range("D35").Value = ws.Range("AJ" & r)
        Range("D37").Value = ws.Range("AL" & r)
        Range("D38").Value = ws.Range("AM" & r)
        Range("D39").Value = ws.Range("AN" & r)
        Range("D40").Value = ws.Range("AO" & r)
        Range("D41").Value = ws.Range("AP" & r)
        Range("D42").Value = ws.Range("AQ" & r)
        Range("D43").Value = ws.Range("AR" & r)
        Rows("23:23").RowHeight = 25.5
    Next r
Application.ScreenUpdating = True
End Sub

 

[titoexcel]

Michale,

I have 260 vendors that i would like to populate in the template and each template to be named as the vendors name..

So if I have XYZ on top 25 sheet

Supplier Name Supplier # Addresss City

I want the supplier XYZ from TOP 25 to populate this template and then save it as separate sheet and move on to the next supplier in the list and do the same.

Are we on the same page?

 

[Michael M]

Did you try the code ??....preferably on a copy of the workbook
The supplier name ...from column "F", will be the name of the sheet, and all the relative data from that row will ber poulated to that vendors sheet.
It will do every name in Column F until it reaches the bottom of the list

 

[titoexcel]

Michael,

I tried the code but it did not work. I am really not sure why as i am not that advanced with the VBA and macros.

 

[Michael M]

but it did not work.

Doesn't help much !
Did you get an error ?
Did it do anything ?
It worked with my sample data