Macro Help needed ASAP

titoexcel

Board Regular
Joined
Mar 26, 2013
Messages
55
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sounds doable....I think it would help if you posted the macro that you already have.
That would save us reinventing the wheel.
 
Upvote 0
Sounds doable....I think it would help if you posted the macro that you already have.
That would save us reinventing the wheel.

Sub InputDataintotemplate()
'
' InputDataintotemplate Macro
'
' Keyboard Shortcut: Ctrl+i
'
Range("B6").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-4]C[-1]"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B12").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-10]C[4]"
Range("B14").Select
Sheets("TOP 25").Select
Columns("F:O").Select
Selection.EntireColumn.Hidden = False
Range("F14").Select
Sheets("QF000542").Select
Range("B14").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-12]C[8]"
Range("B20").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-18]C[4]"
Range("D12").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-10]C[11]"
Range("D13").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-11]C[12]"
Range("D15").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-13]C[14]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-14]C[15]"
Range("D17").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-15]C[16]"
Range("D18").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-16]C[17]"
Range("D20").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-18]C[5]"
Range("D21").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-19]C[1]"
Range("D22").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-20]C[-2]"
Range("D23").Select
Rows("23:23").RowHeight = 25.5
Range("D28").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-26]C[18]"
Range("D29").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-27]C[19]"
Range("B34").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-32]C[23]"
Range("B35").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-33]C[24]"
Range("B37").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-35]C[26]"
Range("B38").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-36]C[27]"
Range("B39").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-37]C[28]"
Range("B40").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-38]C[29]"
Range("B41").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-39]C[30]"
Range("B42").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-40]C[31]"
Range("B43").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-41]C[32]"
Range("D30").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-28]C[20]"
Range("D34").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-32]C[31]"
Range("D35").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-33]C[32]"
Range("D37").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-35]C[34]"
Range("D38").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-36]C[35]"
Range("D39").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-37]C[36]"
Range("D40").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-38]C[37]"
Range("D41").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-39]C[38]"
Range("D42").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-40]C[39]"
Range("D43").Select
ActiveCell.FormulaR1C1 = "='TOP 25'!R[-41]C[40]"
Range("C44:D45").Select
ActiveWindow.SmallScroll Down:=-9
End Sub


Sorry it took me some time, they changed the template on me and I had to re do it and what not.

So at the end I have approximately 261 rows and i would this template to be created in a new tab for each one of these rows. I hope it works otherwise I won't have any sleep for the next 10 days..Thank you
 
Upvote 0
Ok, I read your OP and it seems like a pretty straight forward, easy task. I read your code and I'm scratching my head trying to understand how it relates to populating fields from a data sheet.

So if I understand your task correctly, you want to parse each record of data into a template which will be used to insert new sheets into the file that contains the data! No problem so long as we know what the fields are in the data sheet, and where they go (what fields?) in the template. This requires the template be in a separate file so it can be specified for the Sheets.Add function, OR in the same file to be simply copied for each record. Do the vendor names get used for sheetnames? Where's the vendor names list? (on "Top 25"?) Are the fieldnames also included on "Top 25"?
 
Upvote 0
Ok, I read your OP and it seems like a pretty straight forward, easy task. I read your code and I'm scratching my head trying to understand how it relates to populating fields from a data sheet.

So if I understand your task correctly, you want to parse each record of data into a template which will be used to insert new sheets into the file that contains the data! No problem so long as we know what the fields are in the data sheet, and where they go (what fields?) in the template. This requires the template be in a separate file so it can be specified for the Sheets.Add function, OR in the same file to be simply copied for each record. Do the vendor names get used for sheetnames? Where's the vendor names list? (on "Top 25"?) Are the fieldnames also included on "Top 25"?

Yes i want to parse each record of data into template. Data would come from a sheet called "TOP 25". I linked the fields in the template to the fields on the "TOP 25" sheet and the code I posted should reflect that I believe. I would like the names of vendors to be used for sheetnames. What else you may need in order to help me out with this?
 
Upvote 0
Ok, shortened the macro somewhat, but I'm guessing this is one record for the template based on row 2 in TOP 25 ??
I'm assuming you then need to save this sheet and move onto the next row in TOP 25 ??
How are you going to name the tabs ?, by a cell reference, a name, a number ?? AND where are the names to create these tabs

Code:
Sub InputDataintotemplate()
Sheets("TOP 25").Columns("F:O").EntireColumn.Hidden = False
Range("B6").Formula = "='TOP 25'!A2"
Range("B7").Formula = "=TODAY()"
Range("B12").Formula = "='TOP 25'!F2"
Range("B14").Formula = "='TOP 25'!J2"
Range("B20").Formula = "='TOP 25'!F2"
Range("B34").Formula = "='TOP 25'!Y2"
Range("B35").Formula = "='TOP 25'!Z2"
Range("B37").Formula = "='TOP 25'!AB2"
Range("B38").Formula = "='TOP 25'!AC2"
Range("B39").Formula = "='TOP 25'!AD2"
Range("B40").Formula = "='TOP 25'!AE2"
Range("B41").Formula = "='TOP 25'!AF2"
Range("B42").Formula = "='TOP 25'!AG2"
Range("B43").Formula = "='TOP 25'!AH2"
Range("D12").Formula = "='TOP 25'!O2"
Range("D13").Formula = "='TOP 25'!P2"
Range("D15").Formula = "='TOP 25'!R2"
Range("D16").Formula = "='TOP 25'!S2"
Range("D17").Formula = "='TOP 25'!T2"
Range("D18").Formula = "='TOP 25'!U2"
Range("D20").Formula = "='TOP 25'!I2"
Range("D21").Formula = "='TOP 25'!E2"
Range("D22").Formula = "='TOP 25'!B2"
Range("D28").Formula = "='TOP 25'!V2"
Range("D29").Formula = "='TOP 25'!W2"
Range("D30").Formula = "='TOP 25'!X2"
Range("D34").Formula = "='TOP 25'!AI2"
Range("D35").Formula = "='TOP 25'!AJ2"
Range("D37").Formula = "='TOP 25'!AL2"
Range("D38").Formula = "='TOP 25'!AM2"
Range("D39").Formula = "='TOP 25'!AN2"
Range("D40").Formula = "='TOP 25'!AO2"
Range("D41").Formula = "='TOP 25'!AP2"
Range("D42").Formula = "='TOP 25'!AQ2"
Range("D43").Formula = "='TOP 25'!AR2"
Rows("23:23").RowHeight = 25.5
End Sub
 
Upvote 0
Ok, shortened the macro somewhat, but I'm guessing this is one record for the template based on row 2 in TOP 25 ??
I'm assuming you then need to save this sheet and move onto the next row in TOP 25 ??
How are you going to name the tabs ?, by a cell reference, a name, a number ?? AND where are the names to create these tabs

Code:
Sub InputDataintotemplate()
Sheets("TOP 25").Columns("F:O").EntireColumn.Hidden = False
Range("B6").Formula = "='TOP 25'!A2"
Range("B7").Formula = "=TODAY()"
Range("B12").Formula = "='TOP 25'!F2"
Range("B14").Formula = "='TOP 25'!J2"
Range("B20").Formula = "='TOP 25'!F2"
Range("B34").Formula = "='TOP 25'!Y2"
Range("B35").Formula = "='TOP 25'!Z2"
Range("B37").Formula = "='TOP 25'!AB2"
Range("B38").Formula = "='TOP 25'!AC2"
Range("B39").Formula = "='TOP 25'!AD2"
Range("B40").Formula = "='TOP 25'!AE2"
Range("B41").Formula = "='TOP 25'!AF2"
Range("B42").Formula = "='TOP 25'!AG2"
Range("B43").Formula = "='TOP 25'!AH2"
Range("D12").Formula = "='TOP 25'!O2"
Range("D13").Formula = "='TOP 25'!P2"
Range("D15").Formula = "='TOP 25'!R2"
Range("D16").Formula = "='TOP 25'!S2"
Range("D17").Formula = "='TOP 25'!T2"
Range("D18").Formula = "='TOP 25'!U2"
Range("D20").Formula = "='TOP 25'!I2"
Range("D21").Formula = "='TOP 25'!E2"
Range("D22").Formula = "='TOP 25'!B2"
Range("D28").Formula = "='TOP 25'!V2"
Range("D29").Formula = "='TOP 25'!W2"
Range("D30").Formula = "='TOP 25'!X2"
Range("D34").Formula = "='TOP 25'!AI2"
Range("D35").Formula = "='TOP 25'!AJ2"
Range("D37").Formula = "='TOP 25'!AL2"
Range("D38").Formula = "='TOP 25'!AM2"
Range("D39").Formula = "='TOP 25'!AN2"
Range("D40").Formula = "='TOP 25'!AO2"
Range("D41").Formula = "='TOP 25'!AP2"
Range("D42").Formula = "='TOP 25'!AQ2"
Range("D43").Formula = "='TOP 25'!AR2"
Rows("23:23").RowHeight = 25.5
End Sub

I would like to name the tabs by cell reference B12 from the TOP 25 sheet. Is this all you need?

Thank you
 
Upvote 0
Michael,

This is the latest one I have. They had me change a field and delete some.

Code:
Sub insertsuppliertype()
'
' insertsuppliertype Macro
'
' Keyboard Shortcut: Ctrl+n
'
    ActiveCell.FormulaR1C1 = "Supplier type CORRECT"
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(3, 0).Range("A1").Select
End Sub
Sub fillingouttemplate()
'
' fillingouttemplate Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-4]C[-1]"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-10]C[4]"
    Range("B19").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-17]C[8]"
    Range("B20").Select
    ActiveCell.FormulaR1C1 = "=R[-8]C"
    Range("D12").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-10]C[11]"
    Range("D13").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-11]C[12]"
    Range("D15").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-13]C[14]"
    Range("D16").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-14]C[15]"
    Range("D17").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-15]C[16]"
    Range("D18").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-16]C[17]"
    Range("D20").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-18]C[5]"
    Range("D21").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-19]C[1]"
    Range("D22").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-20]C[-2]"
    Range("D28").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-26]C[18]"
    Range("D29").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-27]C[19]"
    Range("D30").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-28]C[20]"
    Range("A31:D31").Select
    ActiveWindow.SmallScroll Down:=3
    Range("B34").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-32]C[23]"
    Range("B35").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-33]C[24]"
    Range("B37").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-35]C[26]"
    Range("B38").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-36]C[27]"
    Range("B39").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-37]C[28]"
    Range("B40").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-38]C[29]"
    Range("B41").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-39]C[30]"
    Range("B42").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-40]C[31]"
    Range("B43").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-41]C[32]"
    Range("D34").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-32]C[31]"
    Range("D35").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-33]C[32]"
    Range("D37").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-35]C[34]"
    Range("D38").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-36]C[35]"
    Range("D39").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-37]C[36]"
    Range("D40").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-38]C[37]"
    Range("D41").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-39]C[38]"
    Range("D42").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-40]C[39]"
    Range("D43").Select
    ActiveCell.FormulaR1C1 = "='TOP 25'!R[-41]C[40]"
    Range("C44:D45").Select
    ActiveWindow.SmallScroll Down:=-24
End Sub
 
Upvote 0
Ok, you've said the cell reference for naming the tabs is B12, but I was of the understanding you had 261 vendors.
If you are naming each sheet by vendor, where is the list of vendors stored ??
Also, if your bosses are going to keep changing the data that will be copied into the template, I'd suggest you repost when they have finalised their demands.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top