insert columns n times

[luvbite38]

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=211><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><TBODY><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 22.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=30 width=211>Hi,</TD></TR><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 22.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=30>Can someone kindly help me fix the following code pls.

Sub InsertMembers()
Dim y As Integer
  y = Application.InputBox("Number of members", "Number of members", Type:=1)
  If y > 50 Then MsgBox ("The maximum no. of members should NOT be over 50")
Range("V7").Select
  If y <= 50 Then
  ActiveCell.EntireColumn.Copy
    Range(ActiveCell.Offset(1), ActiveCell.Offset(y)).EntireColumn.Insert
     Application.CutCopyMode = False
     End If
End Sub

</TD></TR><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 22.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=30>This code is suppose to add columns n times. However, it is only adding one column for some reasons? Any idea to make it work plssssssss

thanks in advance
</TD></TR><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 22.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=30>Regards,</TD></TR><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 22.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=30>A</TD></TR></TBODY></TABLE>

 

[Biz]

Try below. Have not tested it.

Sub InsertMembers()
Dim y As Integer
  y = Application.InputBox("Number of members", "Number of members", Type:=1)
  If y > 50 Then MsgBox ("The maximum no. of members should NOT be over 50")
Range("V7").Select
  If y <= 50 Then
  ActiveCell.EntireColumn.Copy
    Range(ActiveCell.Offset(1), ActiveCell.Offset(1)).Resize(, y).EntireColumn.Insert
     Application.CutCopyMode = False
     End If
End Sub

Biz

 

[luvbite38]

Thanks Bizz...

it inserted the columns however, got the run time error 1004..

 

[Biz]

I tried and I don't get any errors. Have you protected your worksheet?

Biz

 

[luvbite38]

nah its not protected,,,,

 

[luvbite38]

dude, you were right.... it is working..... idk why it gave me the error when I ran it the first tym..................

my apologies............


Kindest ever regards for sorting me out.....

Ta

 

[Biz]

dude, you were right.... it is working..... idk why it gave me the error when I ran it the first tym..................

my apologies............


Kindest ever regards for sorting me out.....

Ta

Kwel

Biz