Copy previous column range and paste next to last column

Hi, I'm hoping someone can help point me in the right direction with a request I got today.

We've got a spreadsheet with Columns A,B,C,D,E,F,G,H,I,J,K,L where the bold columns make up one month. Currently the five columns are copied and pasted moving left to right. In the above example, the 5 columns would be pasted to Columns M,N,O,P,Q and so on, for the 12 months. The first group of columns are static and are not touched.

What I'm planning on doing is creating another spreadsheet with macros, buttons, a userform perhaps that will interact with the spreadsheet mentioned above. This is to keep the code and so on in a central location.

So far with the help of web searching, I have this code which perfectly copies the last 5 columns then pastes them on the next blank column. Can this code be changed to work from another spreadsheet?

Option Explicit

Sub CopyLastFiveColumns()
Dim LastColumn As Long
LastColumn = Sheets("Sheet1").Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
On Error GoTo 0

Sheets("Sheet1").Columns(LastColumn - 4).Resize(, 5).Select
Selection.Copy

'   Enter the rest of your paste code here

Sheets("Sheet1").Columns(LastColumn + 1).Select
ActiveSheet.Paste

Selection(1).Select
Application.CutCopyMode = False
End Sub

Originally Posted by askpcguy

Can this code be changed to work from another spreadsheet?

Yes it can.

Thing is, you want to make changes to a working code, to do that, you should give more details. Running it for a different spreadsheet is simply changing Sheet1 to the name of the other spreadsheet.

Sheets("Sheet1")

But, by 'spreadsheet', do you mean a new worksheet, or a new workbook? If you meant a workbook, the code will get slightly more tedious.

Hi, askpcguy,

the first code could be like

Sub CopyLastFiveColumns()
Dim LastColumn As Long
With Sheets("Sheet1")
  LastColumn = .Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
  .Columns(LastColumn - 4).Resize(, 5).Copy .Columns(LastColumn + 1)
  Application.CutCopyMode = False
End With
End Sub

I think we all know how an empty workbook will look like, you would need to specify if you only want one sheet in there for example.

Sub CopyLastFiveColumnsOtherSheet()
Dim LastColumn As Long
With Sheets("Sheet1")
  LastColumn = .Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
  Workbooks.Add
  .Columns(LastColumn - 4).Resize(, 5).Copy
  ActiveSheet.Paste
  Application.CutCopyMode = False
End With
End Sub

Ciao,
Holger

In the attached file, I've made a simple form that allows you to choose to paste for an already open workbook, or a closed workbook saved somewhere. Try it and see how it goes. You can make further changes from there.

Change the code for button1 to this and it should work as you wanted, but note that it pastes into the activesheet of the target workbook.

Private Sub CommandButton1_Click()
    Dim f, v, wb As Workbook, wb2 As Workbook, LastColumn As Long, op As Boolean
    
    If Me.TextBox1.Value = "" And Me.ListBox1.ListIndex < 0 Then
        MsgBox "Select an open workbook, or browse to a workbook to be opened!"
        Exit Sub
    ElseIf Me.TextBox1.Value <> "" Then
        f = Me.TextBox1.Value
        Set wb = Application.Workbooks.Open(f)
    Else
        op = True
        f = Me.ListBox1.Value
        v = Split(f, "\")
        If UBound(v) > 0 Then f = v(UBound(v))
        Set wb = Application.Workbooks(f)
    End If
    
    Set wb2 = ActiveWorkbook
    
    With wb.ActiveSheet
        LastColumn = .Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
        On Error GoTo 0
        
        '.Columns(LastColumn - 4).Resize(, 5).Copy .Columns(LastColumn + 1)
        wb2.ActiveSheet.Columns(1).Resize(, 5).Copy .Columns(LastColumn + 1)
        
        '   Enter the rest of your paste code here
        
        Application.CutCopyMode = False
    End With
    
    wb.Save
    If Not op Then wb.Close
    Set wb = Nothing
    Set wb2 = Nothing
    MsgBox "Columns copied!"
End Sub

Hi askpcguy, I have no idea what you are after now. Also, I have no idea how your data structure looks like just by looking at your recorded macro for one month. I suggest starting a fresh thread as this is deviating from your original post already.