-3

I am trying to automate an insert row and copy if cell has "(4 SHEETS)" in Cell A for example. I'm not sure how to get started with this.

If aCell contains "(x SHEETS)" then insert x number of rows below aCell. Copy aCell. Paste Variables into x rows. Next

Ultimately I would like the "(x SHEETS)" to get renamed also. So "(4 SHEETS)" would turn into 4 rows with "(4 SHEETS)" being modified to "(SHEET 1)", "(SHEET 2)" etc.

Thanks for any help

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Matt Taylor
  • 521
  • 1
  • 11
  • 26

1 Answers1

2

Assuming aCell location is in A1 (cell address [1,1]) then get the aCell value, extact the number from aCell value, then create number of rows based on aCell value.

Sub Main()

   Dim aCell As String
   Dim ws As Worksheet
   Dim i As Long
   Dim noOfSheet As String

   Set ws = ThisWorkbook.Sheets("Sheet1")
   aCell = ws.Cells(1, 1).Value

   'Extract the no. of sheets from the cell
   For i = 1 To Len(aCell)
       If Mid(aCell, i, 1) >= "0" And Mid(aCell, i, 1) <= "9" Then
           noOfSheet = noOfSheet + Mid(aCell, i, 1)
       End If
   Next

   'Insert no. of row based on the no. of sheets
   If CInt(noOfSheet) > 0 Then
       For i = 1 To CInt(noOfSheet)
           ws.Cells(1, 1).Offset(1, 0).EntireRow.Insert
           ws.Cells(1, 1).Offset(1, 0).Value = "(SHEET " & CInt(noOfSheet) + 1 - i & ")"
       Next i
   End If

End Sub
Keenlearner
  • 704
  • 1
  • 7
  • 21
  • 1
    Ok so this adds the "(Sheets x)" below "A1" regardless of where the row with the # was. and only if the cell only has a #. My row is something like "FOUNDATION BILL OF MATERIAL (4 SHEETS)". This is on the right track tho. Thanks a bunch for starting me out. – Matt Taylor Jul 20 '17 at 03:12
  • yup sure, no problem :) – Keenlearner Jul 20 '17 at 03:13
  • To get to the right part of the cell value, you'll need to build in some search logic within the query. Suggesting you follow some of the tips in this article to help you get the right sheet number. https://stackoverflow.com/questions/18617175/check-whether-a-cell-contains-a-substring – Maus Jul 20 '17 at 11:52