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]

Thanks for your reply fluff, still no luck.

It doesnt delete the columns between col AB and the last col before row 5 is blank for every 6th cell.

Also, the paste valuing doesnt seem to be working correctly as it partially past values into the Col AB. Any thoughts?

 

[Fluff]

Could you please post some sample data showing the layout of you sheet & the expected outcome?

There are some tools here to help with that.
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Hello1991]

Excel 2007 32 bit
	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
3
4
5	13/01/2018 23:46						09/01/2018 23:37
6	Home Team	Home	Draw	Away	Away Team		Home Team	Home	Draw	Away	Away Team
7	Team 1	4	9	4	Team 6		Team 1	4	9	4	Team 6
8
9
10	Team 2	3	8.5	3	Team 7		Team 2	3	8.5	3	Team 7
11
12
13	Team 3	2	8	2	Team 8		Team 3	2	8	2	Team 8
14
15
16	Team 4	1	16	1	Team 9		Team 4	1	16	1	Team 9
17
18
19	Team 5	5	8	5	Team 10		Team 5	5	8	5	Team 10
Sheet1 (2)

 

[Fluff]

Which columns do you want to delete in the above example?

 

[Hello1991]

So each section is 5 columns wide, e.g. AB:AF. These sections continue to the right of the sheet as per Col AH above and so on. I want the macro to check for every 6th cell in row 5 (highlighted in yellow) and where it is the last cell in row 5 to be populated, I want to delete all columns between that prior column and col AB.

Using the example above, let's sat cell AH5 is the last cell in row 5 to be populated after AB5. Therefore, i want to delete all columns from AG to AB inclusive, and hence I'm left with cols AH:AL now in cols AB:AF. Hope that makes sense. Thanks

 

[Fluff]

Try

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 - 1)).EntireColumn.Delete
   Worksheets("Input Page").Columns("B:F").ClearContents
           
   Call Test
End Sub

 

[Hello1991]

Great Fluff, thanks for that.Just want to add a few things to it to finish it off.

1.Insert code that will copy columns B:F and paste value in same columns for 'mysheet1' . I want it to do this just before it clears the contents in the Input Page.
2. When it copies the new sheet with todays date, I would like it to place the new sheet beside the Input Page tab.
3. Then for another macro already being used, I want to update it, see code below.

Sub Macro20(mysheet As String)
With Worksheets(mysheet)
Calculate
.Columns("AB:AB").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Columns("H:L").copy
.Columns("AB:AB").Insert Shift:=xlToRight
.Columns("AB:AF").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
Sheets("Input page").Columns("B:G").ClearContents
End Sub

Here, I would like the macro to check if the 6th cell from AB5 is populated, i.e. cell AH5, and if it is, do the macro as above, but if not copy the cols H:L and paste value in cols AB:AF.


Any help would be greatly appreciated, thanks a mill!

 

[Fluff]

1) Do you want to copy cols B:F from the new sheet to mysheet1, or just convert those cols on mysheet1 to values?
2) Before of after Input page?

 

[Hello1991]

Just convert Cols B:F on mysheet to values.
After the input page.
Thanks a mill.

 

[Fluff]

Sub Macro40(mysheet1 As String)

   Dim UsdCols As Long
   
   With Worksheets(mysheet1)
      .Copy After:=Sheets("Input Page")
      .Range("B:F").Value = .Range("B:F").Value
   End With
   
   ActiveSheet.Name = WorksheetFunction.Text(Now(), "dd-mm-yyyy")
   
   UsdCols = Cells(5, Columns.Count).End(xlToLeft).Column
   Range(Cells(1, 29), Cells(1, UsdCols - 1)).EntireColumn.Delete
   
   Worksheets("Input Page").Columns("B:F").ClearContents
           
   Call Test
End Sub
Sub Macro20(mysheet As String)
   With Worksheets(mysheet)
      If .Range("AH5") <> "" Then
         Calculate
         .Columns("AB:AB").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
         .Columns("H:L").Copy
         .Columns("AB:AB").Insert shift:=xlToRight
         .Columns("AB:AF").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
         Application.CutCopyMode = False
      Else
         .Range("H:L").Copy
         .Range("AB1").PasteSpecial xlPasteValues
      End If
   End With
   Sheets("Input page").Columns("B:G").ClearContents
End Sub