VBA copy/paste columns loop

[SirAsksAlott]

I'm a complete newbie at VBA, and I',m trying to automate a report for work.

I have a worksheet that has column headers as dates, with each new column being a week apart with the final column summing up all previous weeks with the header (YTD)

To update this report with the new week, I simply insert a new column, copy the previous column, and paste over all columns to the right except for the very last column (YTD column). The worksheet will then update itself.

For example:

5/9	5/16	YTD
$ 1	$ 1	$ 2
$ 2	$ 1	$ 3

<tbody>
</tbody>

If I insert at column "5/16", I will have a blank column in between 5/9 and 5/16, and will copy column 5/9 over the new blank column which will change to 5/16 automatically, and the previous 5/16 column data will automatically change to 5/23 with the last column YTD summing up the new data. The data that updates itself automatically is already a working function in this workbook and does not need to be included in the VBA.

The VBA code would have to either: A) recognize and stop the loop at YTD column, or B) simply stop the paste loop before the last column.

I can't use references like columns A:G since everytime this report is sent out there will be an additional column, changing everything to the right of the insert column macro.

Any help please? I hope this was clear. Thanks!

 

[SirAsksAlott]

For the record this is what I have so far:

Sub SQLRefresh()
'
' SQLRefresh Macro
'


'
ActiveWorkbook.RefreshAll
End Sub


Sub RunWarrantyDemand()
Run "SQLRefresh"


End Sub


Sub sbInsertingColumns()
'Inserting a Column at Column U
Range("U1").EntireColumn.Insert
Columns("T").Copy


End Sub

 

[SirAsksAlott]

After hours of learning how VBA works, this is what I got and it almost works. What am I doing wrong? I get an error 1004 "This operation is attempting to shift cells in your workbook."

Well, thats what I want it to do; insert a new column.

Sub InsertingNewColumns()




Dim ws As Worksheet

For Each ws In Worksheets

Select Case UCase(ws.CodeName)
Case "DATA PASTE"
Case Else
With ws
Do

.Range("F1").EntireColumn.Insert
.Range("E:E").Copy

.Range("F:F").PasteSpecial xlPasteAll

.Range("F:F").Copy
If .Range("G3").Value <> "YTD" Then Exit Do
.Range("G:G").PasteSpecial xlPasteAll


Loop

End With
End Select




Next ws



End Sub