Find Inserted Column - Paste Into

**Buddy7** · 08-14-2009, 02:19 PM

I want to append to this macro below so that it will know to paste data into a column that I just inserted in worksheet “Count”. In the macro below I put Column G, but the new column I insert won’t always be Column G. In other words, assume I just inserted a new column in worksheet “Count”. I would like to make the macro know to paste what I copied from worksheet “Number” into Rows 4:20 of that new Column in worksheet “Count” whatever column it may be. Can you assist me with this?

Sub FindColumn()
Sheets("Number").Activate
Range("E4:E20").Copy
Sheets("Count").Activate
Range("G4:G20").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

**mojo249** · 08-14-2009, 08:47 PM

Hi Buddy

On your worksheet "Count", will there ever be any other empty columns aside from the one you've just inserted?

Dion

**Buddy7** · 08-16-2009, 12:09 PM

Yes, but they will all be a couple columns over to the right. The newly inserted column will always be sandwich between columns with data in them.

**JBeaucaire** · 08-16-2009, 12:59 PM

Are you manually inserting this column? If a macro is doing it, the macro can store where the blank column is.

To clarify mojo's question, this blank column will ALWAYS be the first blank column in the data?

If so, maybe this:

Sub FindColumn()
   Sheets("Number").Range("E4:E20").Copy
   Sheets("Count").Range("A1").End(xlToRight).Offset(3, 1).PasteSpecial (xlPasteValues)
End Sub

**Buddy7** · 08-27-2009, 05:08 PM

My apologies for the delay, yes a macro is inserting the blank column, how can I get the macro to store where the blank column is?

**JBeaucaire** · 08-28-2009, 07:53 AM

The post above shows how to directly insert the new data into the next blank column.

Here's a way to do that by first determining the next column (NC) in a variable:

Option Explicit

Sub FindColumn()
Dim NC As Long
NC = Sheets("Count").Range("A1").End(xlToRight).Row

   Sheets("Number").Range("E4:E20").Copy
   Sheets("Count").Cells(4, NC).PasteSpecial xlPasteValues
   
End Sub

**Buddy7** · 08-28-2009, 05:51 PM

The contents are getting pasted into Column A instead of the newly inserted column or I am getting an error message. Any input is appreciated. Maybe I am making a mistake.

In this code, I have the data going into Column A.

Option Explicit

Sub FindColumn()
Dim NC As Long
NC = Sheets("Count").Range("A1").End(xlToRight).Row

   Sheets("Number").Range("E4:E20").Copy
   Sheets("Count").Cells(4, NC).PasteSpecial xlPasteValues
   
End Sub

In the code below, I am getting an error message and this line of code is being highlighted. Sheets("Total").Range("A1").End(xlToRight).Offset(3, 1).PasteSpecial (xlPasteValues)

Sub FindColumn()
   Sheets("Number").Range("E4:E20").Copy
   Sheets("Count").Range("A1").End(xlToRight).Offset(3, 1).PasteSpecial (xlPasteValues)
End Sub

Sorry if its my fault. Thanks for your help.

**JBeaucaire** · 08-28-2009, 06:13 PM

First there's an error. NC should equal .Column + 1, not .Row

Option Explicit

Sub FindColumn()
Dim NC As Long
NC = Sheets("Count").Range("A1").End(xlToRight).Column + 1

   Sheets("Number").Range("E4:E20").Copy
   Sheets("Count").Cells(4, NC).PasteSpecial xlPasteValues
   
End Sub

[/code]
Rookie mistake, my apologies.

As for your second line of code, I don't use that offset syntax, nor the way you're using xlpastevalues. Does fixing my error above allow you to use the code I gave you? My code attempts to paste the copied cell into the the next empty column starting in row4. It should work with the fix above.