I have the following macro that inserts a new column and pastes SLS into
the new column. The way the macro is currently set is to paste it into
rows 2-33.

I need it to paste SLS into all rows in Column G that are not blank
(this can vary from 1 to 500 rows).

Here's the current macro. I'd appreciate any advice
Thanks!

Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "SLS"
Range("C1").Select
Selection.Copy
Range("C2:C33").Select
ActiveSheet.Paste


--
TimmerSuds
------------------------------------------------------------------------
TimmerSuds's Profile: http://www.excelforum.com/member.php...o&userid=35596
View this thread: http://www.excelforum.com/showthread...hreadid=553818

TimmerSuds Wrote:
I need it to paste SLS into all rows in Column G that are not blank
(this can vary from 1 to 500 rows).
Not Blank? or blank? How will you know how many rows there are? you
need a column that you know will have data in the last row. I'll
assume that Column A has data in every row. Also, is the data in col G
before you insert the new column or after? You will need to swap the
code around if it is before.


Sub UpdateSLS()
Dim myRange As Range
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Value = "SLS"
Range("C2:C33").Value = "SLS"
LastRow = Range("A600").End(xlUp).Row
myAddress = "G1:G" & LastRow
Set myRange = Range(myAddress)
For Each cell In myRange
If cell.Value < "" Then cell.Value = "SLS"
Next cell

End Sub


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=553818

Hi,
Try this code:

Dim Iloop as Integer
Sub PasteSLS()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
For Iloop = 1 to 500
If Not IsEmpty(Cells(Iloop,"C")) then
Cells(Iloop,"C") = "SLS"
End IF
Next Iloop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Your code has column C but your message talks about column G? If it is
column G, you'll need to code the code accordingly.

--
Ken Hudson


"TimmerSuds" wrote:


I have the following macro that inserts a new column and pastes SLS into
the new column. The way the macro is currently set is to paste it into
rows 2-33.

I need it to paste SLS into all rows in Column G that are not blank
(this can vary from 1 to 500 rows).

Here's the current macro. I'd appreciate any advice
Thanks!

Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "SLS"
Range("C1").Select
Selection.Copy
Range("C2:C33").Select
ActiveSheet.Paste


--
TimmerSuds
------------------------------------------------------------------------
TimmerSuds's Profile: http://www.excelforum.com/member.php...o&userid=35596
View this thread: http://www.excelforum.com/showthread...hreadid=553818

You code can be greatly simplified but you never told us how to determine
the last row.
Unless?, you really did mean the last row in col G instead of C. Then use
the 2nd.

Sub insertandfill()
Columns(3).Insert
Range("c1").Value = "SLS"
Range("c1").AutoFill Destination:=Range("c1:c33")
End Sub

Sub insertandfill()
lr=cells(rows.count,"g").end(xlup).row
Columns(3).Insert
Range("c1").Value = "SLS"
Range("c1").AutoFill Destination:=Range("c1:c" & lr)
End Sub

--
Don Guillett
SalesAid Software

"TimmerSuds" wrote
in message ...

I have the following macro that inserts a new column and pastes SLS into
the new column. The way the macro is currently set is to paste it into
rows 2-33.

I need it to paste SLS into all rows in Column G that are not blank
(this can vary from 1 to 500 rows).

Here's the current macro. I'd appreciate any advice
Thanks!

Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "SLS"
Range("C1").Select
Selection.Copy
Range("C2:C33").Select
ActiveSheet.Paste


--
TimmerSuds
------------------------------------------------------------------------
TimmerSuds's Profile:
http://www.excelforum.com/member.php...o&userid=35596
View this thread: http://www.excelforum.com/showthread...hreadid=553818