VBA to paste into a table

[Fester675]

I already have a macro to copy & paste a range on 1 worksheet to a table on another sheet. However, I want to either....

a) paste into the next available column

or

b) paste into the column with the previous month's heading, based on today's date.

Any ideas? HELP!

 

[mrmmickle1]

Before:

Excel 2010
	A	B	C	D	E	F
1	Header	Header	Header	Header	Header
Sheet1

After:

Excel 2010
	A	B	C	D	E	F
1	Header	Header	Header	Header	Header	My New Cell Value
Sheet1

Code to find next open column:

[COLOR=#0000ff]Sub [/COLOR]Test()


 [COLOR=#0000ff]   Dim [/COLOR]LCol [COLOR=#0000ff]As Integer[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] myNewVal [COLOR=#0000ff]As String[/COLOR]
    
    myNewVal = "My New Cell Value"
    LCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(1, LCol + 1) = myNewVal


[COLOR=#0000ff]End Sub[/COLOR]

Find Last Month Column (If dates are end of month values):

Excel 2010
	A	B	C	D	E
1	1/31/2017	2/28/2017	3/31/2017	4/30/2017	5/31/2017
Sheet1

[COLOR=#0000ff]Sub [/COLOR]FindPreviousMonthCol()


 [COLOR=#0000ff]   Dim [/COLOR]PrevMonthEnd[COLOR=#0000ff] As Date[/COLOR]
    [COLOR=#0000ff]Dim [/COLOR]rngMyCol[COLOR=#0000ff] As[/COLOR] Range
   [COLOR=#0000ff] Dim[/COLOR] myCol [COLOR=#0000ff]As Integer[/COLOR]
    
    PrevMonthEnd = Format(DateAdd("d", -1, DateSerial(Year(Date), Month(Date), 1)), "DD/MM/YYYY")
  [COLOR=#0000ff]  Set [/COLOR]rngMyCol = Rows(1).Find(PrevMonthEnd)
    myCol = rngMyCol.Column
    
[COLOR=#0000ff]End Sub[/COLOR]

This returns 4 for myCol

 

[Fester675]

Thanks for this mrmmickle1!
I presume I would enter either of these codes after the paste code - or before the End Sub?

 

[mrmmickle1]

I believe so. Not quite sure since I don't know what your code looks like. You may have to fiddle around with the procedures a bit if your scenario does not involve data in Row 1. Just let me know if you run into issues. I'm happy to help.