VBA - Insert, Copy and Paste macro but run from different worksheet

[Hello1991]

Hello all! Many thanks for your help to date on this forum. I'm a novice to VBA so apologies if this post is too simple.

On a particular worksheet ("sheet1"), I'm trying to re-calculate the workbook, insert a column in specific column (column AB) and then copy and paste certain columns (columns H:L) within the same worksheet to other columns within that worksheet, and finally clear contents (Columns B:G) in another worksheet ("Input Page"). I then try and assign this macro to a button on a different worksheet ("Input Page").

However, the problem is that when i run the macro on the other sheet, it only copy and pastes the columns in the active worksheet that I click the button. I think it's something to do with my cell referencing. See my code below which was produced using the Record Macro option in the Developer tab. Thanks a million for your help!
Sub Macro11()
'
' Macro11 Macro
Calculate
Columns("AB:AB").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("H:L").Select
Selection.copy
Columns("AB:AB").Select
Selection.Insert Shift:=xlToRight
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Input page").Select
Columns("B:G").Select
Selection.ClearContents
End Sub

Sub Run_Macro() ' contained in worksheet 'Input Page'
Call Macro11
End Sub

 

[Hello1991]

Hi Fluff, it just gives me the run time error 'Subscript out of range'.

If i replace the line 'Worksheets("fluff")' with 'Worksheets("Input Page")', it gives me the sheet name in the message box that is selected as per cell (1,18). Any thoughts please?

P.S. How do you insert a code box in a reply?

 

[Fluff]

If i replace the line 'Worksheets("fluff")' with 'Worksheets("Input Page")',

Apologies, forgot to change the sheet name back after testing.
After changing the sheet name, was the relevant sheet activated & did macro40 run successfully?

P.S. How do you insert a code box in a reply?

It's the # icon in the reply window. I click on the icon & then paste the code in between the tags that appear.

 

[Hello1991]

Hi Fluff, thanks for that, works great now. Can I ask why did it work by using inluding the msg box line please?

'm onto another predictament. So with that same macro 40 as above, I'm trying to call another sub within it (sub 50) which I want it to do the following:
- For the sheet which I've copied and renamed, delete all columns from column AB up to the last section that is populated.
For example, from column AB onwards there are a number of sections, the number varies on the worksheet, where each section is 6 columns wide. So the first section is 6 columns wide from column AB to AF, then one column as a space before the next section and then the second section from Column AH onwards, and so on.

So i want it to delete, from column AB, all the sections just before the very last section that is populated. Any thoughts? Hopefully the above makes sense. Thanks as always.

 

[Fluff]

Can I ask why did it work by using inluding the msg box line please?

It doesn't "make it work", it's simply used as a check to ensure that the value is correct.

As for the rest, I'm afraid I don't understand what you are after.
Could you please try re-explaining?

 

[Hello1991]

Hi Fluff, apologies for the delay in replying and my communication above.

Two queries please.
1. Trying to insert code that will copy and paste specific columns for 'mysheet1' but it doesnt seem to work. See my comment half way down in my code below. Any thoughts?

Sub Macro40(mysheet1 As String)
With Worksheets(mysheet1)
    .copy After:=Sheets(Sheets.Count)
    
    ActiveSheet.Name = _
    WorksheetFunction.Text(Now(), "dd-mm-yyyy")
    
    'ActiveSheet.Columns("B:F").ClearContents
    'Need to paste value B-F on sheet that have copied after copied and before clear contents on input page
    
   ActiveSheet.Columns("AB:PX").ClearContents
    
   'Trying to copy, past value cols of sheet chosen i.e. mysheet1, before clears contents from Input Page
   With Worksheets(mysheet1).Columns("B:G").copy
        Columns("B:G").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
   End With
   
    Worksheets("Input Page").Columns("B:F").ClearContents
        
End With
Call Test
End Sub

2. New macro. I'm trying to create a new macro but running into some difficulty.

This macro applies to the sheet chosen from cell R3 of the ‘Input Page’ tab.
For the new sheet created by macro 40 (see above), I now want it to look for the first cell in row 5 that is populated from col AB onwards. When it finds the last col (in row 5) that is populated, I want it to delete all columns from the column before that column, as far back as column AB.

For example, my new sheet that macro 40 has created is 20_01_2018. I want the macro to search for the last column in row 5 that is populated, say Col CD, and I want to delete all the columns between CC and AB. Note AB never changes.
When this happens, I then want to copy and paste the value from column H:L of the same sheet and paste value the columns to Cols AB:AF of the same sheet.
Any thoughts?

As always, thanks a million for your help.

 

[Fluff]

How about

Sub Macro40(mysheet1 As String)

   Dim UsdCols As Long
   
   Worksheets(mysheet1).Copy After:=Sheets(Sheets.Count)
   ActiveSheet.Name = WorksheetFunction.Text(Now(), "dd-mm-yyyy")
   
   UsdCols = Cells(5, Columns.Count).End(xlToLeft).Column
   Range(Cells(1, 29), Cells(1, UsdCols - 2)).EntireColumn.Delete
   Range("H:L").EntireColumn.Copy
   Range("AB1").Insert
   
   Worksheets("Input Page").Columns("B:F").ClearContents
           
   Call Test
End Sub

 

[Hello1991]

Thanks Fluff, nearly works. Apologies I should have been more clear.

When I say I want it to find the first cell that is populated in row 5, I mislead you. From cell AB5 onwards, only every 6th cell to the right may be populated, for example AB5, AH5, AN5, AT5 and so on. So I want to find the first cell from this range where the cell is not populated. Then apply as above, i.e. delete columns.

Would you be able to include my first query in your code also please?

For the last part, instead of inserting the copied columns, I'd like to past value over the column AB field? Thanks a million!

 

[Fluff]

Not sure I understand.
Do you mean ignore AB5 to AG5, but if AH5 is blank delete AC:AF

 

[Hello1991]

No, apologies.

If AB5 is blank, copy and paste cols H:L, otherwise check for first cell in range AB5 to DZ5 that is blank and when you find that cell, delete all columns in the column before that cell all the way back to column AB inclusive. Does that make sense?

 

[Fluff]

How about

Sub Macro40(mysheet1 As String)

   Dim UsdCols As Long
   
   Worksheets(mysheet1).Copy After:=Sheets(Sheets.Count)
   ActiveSheet.Name = WorksheetFunction.Text(Now(), "dd-mm-yyyy")
   
   If Cells(5, 28) = "" Then
      UsdCols = 28
   Else
      UsdCols = Range(Cells(5, 27), Cells(5, 16384)).SpecialCells(xlBlanks)(1).Column
      Range(Cells(1, 28), Cells(1, UsdCols - 1)).EntireColumn.Delete
   End If
   Range("H:L").EntireColumn.Copy Range("AB1")
   Worksheets("Input Page").Columns("B:F").ClearContents

   Call test
End Sub