How do I insert string of text?

tschaap

New Member
Joined
Jul 5, 2007
Messages
27
I have a long Excel spreadsheet, with 75 worksheets, one after another, going down. Instead of teadiously copying & pasting info on each page, is there a way to create a formula that will insert my text, for example "Always your best deal", at a specific location on each of those 75 pages? The info I want to insert is exactly 111 rows below the first one. So, every 111 rows, I want to insert this line, in column H, 75 times. Is that possible?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try this on a copy of your workbook, to ensure that there is no irrepairable damage!

As long as you require your text in the same cell in each worksheet.

Select the first work sheet, hold down 'Shift Key' and select last work sheet.

Type your text in required cell and click 'Enter'.

Your text should be on every sheet.

Kelbo
 
Upvote 0
You can select all those sheets, type the text you want in the appropriate cell on one of them, and it will be entered in that cell on every selected sheet.

WARNING: Be sure to ungroup before typing anything that you don't want populated everywhere. Otherwise you can accidentally replace data in every sheet (ask me how I know....)
 
Upvote 0
Thank you so much for your reply, but, I guess I wasn't very specific. This is all being done on one excel spreadsheet. In that one worksheet, starting at row 180, in Column H, I'm entering a phrase, "Always your best deal!" (It actually goes across 7 cells on the spreadsheet, H to N)) I'd like to build a formula that let's me enter it once and have that phrase inserted every 111 rows going down that worksheet. So, at row 291 the phrase is inserted into H column, at row 402 it's inserted into the H column and so on into row 513, then row 624, etc.

I'm going down the excel spreadsheet 75 times, clear down to row 8,283. I have made up quote worksheets that I fill in as needed. Each Excel workbook I have has 3 tabs, for example one for January, one for Feb and one for March. Each one of these has 75 individual quote sheets, clear down to row 8,283. So you can see, filling them each one at a time will take forever.

I hope maybe this explains it better?
I sure appreciate your help on this, this will help this old guys' work go a lot faster!!!
 
Upvote 0
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Well, you can achieve this task using the VBA macro code
try following macro code
Code:
  Private Sub mySub()<o:p></o:p>
      Dim row As Integer, col As Integer<o:p></o:p>
      Dim i As Integer<o:p></o:p>
      <o:p></o:p>
      row = 111<o:p></o:p>
      col = 8 'for column H<o:p></o:p>
      Dim wsheet As Worksheet<o:p></o:p>
          <o:p></o:p>
      For i = 1 To ThisWorkbook.Worksheets.Count<o:p></o:p>
          Set wsheet = ThisWorkbook.Worksheets(i)<o:p></o:p>
          wsheet.Cells(row, col).Value = "Always your best deal"<o:p></o:p>
      Next<o:p></o:p>
  End Sub
 
Upvote 0
Chris, thanks for the reply. Your answer looks like it might work, except, I've not worked with Code much at all. How do I put it into my worksheet? I've enabled macro's and the window to start one, I'm just not sure from there what to do.

And, it's late here now, in Idaho, so I'll be working on this in the morning.
Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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