Using Excel 365, my VBA works, but I am curious about the With/End with section. I always assumed that I needed a "." to precede my entries within a With/End With statement. Why doesn't my code have the same need for a "dot" to precede the code? (I had to look online for some help with this and found that the code that worked did not have a dot). But why?
Lastly, is there a way to post code that includes the proper tabbing? This site removed my formatting when I pasted it in.
Sub With_No_Dot()
Dim CompInfo(1 To 8, 1 To 2)
Dim r As Byte, c As Byte
Const StartRow As Long = 6
Dim NewBook As Workbook
Dim ShNew As Worksheet
For r = 1 To 8
For c = 1 To 2
CompInfo(r, c) = Cells(StartRow + r, c).Value
Next c
Next r
Set NewBook = Workbooks.Add
For r = 1 To 8
With NewBook
Set ShNew = .Sheets.Add
ShNew.Name = CompInfo(r, 1)
Range("A1").Value = CompInfo(r, 1)
Range("A2").Value = CompInfo(r, 2)
End With
Next r
End Sub
Excel 2016 (Windows) 64 bit
<tbody>
</tbody>
<tbody>
</tbody>
Lastly, is there a way to post code that includes the proper tabbing? This site removed my formatting when I pasted it in.
Sub With_No_Dot()
Dim CompInfo(1 To 8, 1 To 2)
Dim r As Byte, c As Byte
Const StartRow As Long = 6
Dim NewBook As Workbook
Dim ShNew As Worksheet
For r = 1 To 8
For c = 1 To 2
CompInfo(r, c) = Cells(StartRow + r, c).Value
Next c
Next r
Set NewBook = Workbooks.Add
For r = 1 To 8
With NewBook
Set ShNew = .Sheets.Add
ShNew.Name = CompInfo(r, 1)
Range("A1").Value = CompInfo(r, 1)
Range("A2").Value = CompInfo(r, 2)
End With
Next r
End Sub
Excel 2016 (Windows) 64 bit
A | B | |
---|---|---|
6 | Company | Manager |
7 | Jellyfish | Camila Wilson |
8 | Dasring | Greg Earnest |
9 | Fightrr | Walter Jackson |
10 | Kryptis | Margaret Rivera |
11 | Perino | Eddie Chen |
12 | Twistrr | Rachel Crosby |
13 | Hackrr | Anthony Welch |
14 | Pes | Norma Ashley |
<tbody>
</tbody>
Sheet: Activity |
<tbody>
</tbody>
Last edited: