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

 

[Fluff]

Hi & welcome to the board
How about

Sub Macro11()
'
' Macro11 Macro
With Sheets("MA")
   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

 

[Hello1991]

That works perfect fluff, thanks a million!

I'm now trying to make the macro choose the sheet as per selection in a dropdown list in the 'Input Page' sheet in cell A1. I've created a macro that activates the sheet as per the cell value and then i call that in the main macro - however it won't work for me as it says I need to define an object. See my code below - would you be able to advise please?

Sub ActivateSheetsByValue()
Dim mySheet As String
mySheet = Worksheets("Input Page").Cells(1, 1).Value
Worksheets(mySheet).Activate
End Sub

Sub Macro12()
'
' Macro12 Macro
With 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

 

[Fluff]

Try

Sub ActivateSheetsByValue()
Dim mysheet As String
mysheet = Worksheets("Input Page").Cells(1, 1).Value
Worksheets(mysheet).Activate
Call Macro12(mysheet)
End Sub

Sub Macro12(mysheet As String)
'
' Macro12 Macro
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

 

[Hello1991]

Thank you fluff, that works brilliantly - many thanks!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Hello1991]

Fluff, another issue which I need your help on please?

I've created a data validation list contained within cell(1,1) on the 'Input Page' sheet which contains a list of all the worksheets within the workbook. I'm trying to then run the macro I've created (as per your email above) and apply that macro to the selected sheet as per the data validation list, i.e. cell(1,1). However, I'm getting an error saying that the subscript is out of range. Now some of the sheets are in a custom date format, i.e. "dd-mm-yyyy" so I'm wondering if this has anything to do with it?

Sub ActivateSheetsByValue()
Dim mysheet As String
mysheet = Worksheets("Input Page").Cells(1, 1).Value --- Tripping up here and wont run for the updated sheet chosen in cell(1,1) from the validation list
Worksheets(mysheet).Activate
Call Macro12(mysheet)
End Sub

Sub Macro12(mysheet As String)
'
' Macro12 Macro
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

 

[Fluff]

What is the value in A1 when it fails?

 

[Hello1991]

Apologies Fluff, I probably was not clear in my last mail.

The value in cell A1 is the value chosen from the data validation list (i.e. a list which contains the name of all the sheets in workbook). So I want to choose the sheet with todays date, say '11-01-2018' and I want the macro 'SubActivateSheetsByValue' to run. This macro takes the sheet chosen in cell A1 and then applies the macro 12. However I get the message that the subscript is out of range.

I just noticed that I changed the structure around a bit in my code so the data validation list is now in cell R1 of the Input Page tab, and not cell A1 as referenced above. Everything else remains the same except for this reference change.

For reference, the code I used to create a new copy sheet is as below. Problem is that I cant run the 'SubActivateSheetsByValue' macro to the chosen sheet.
'Creates new sheet,clears contents and updates data validation listing
Sub ActivateSheetsByValue2()
Dim mysheet1 As String
mysheet1 = Worksheets("Input Page").Cells(1, 18).Value
Worksheets(mysheet1).Activate
Call Macro40(mysheet1)
End Sub

Sub Macro40(mysheet1 As String)
'
' Macro12 Macro

With Worksheets(mysheet1)
.copy After:=Sheets(Sheets.Count)

ActiveSheet.Name = _
WorksheetFunction.Text(Now(), "dd-mm-yyyy")

ActiveSheet.Columns("B:F").ClearContents
ActiveSheet.Columns("AB:PX").ClearContents

End With
Call Test
End Sub

'Macro to create data validation listing of sheets
Sub Test()
Dim wsInputPage As Worksheet
Dim r As Integer
Dim ws As Worksheet
Set wsInputPage = Worksheets("Input Page")
r = 0
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> wsInputPage.Name Then
r = r + 1
wsInputPage.Cells(r, 19) = ws.Name
End If
Next ws
With wsInputPage
.Range(.Cells(1, 19), .Cells(r, 19)).Name = "List"
With .Cells(1, 18).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List"
End With
End With
End Sub

 

[Fluff]

Put a msgbox in like this & see what it says

Sub ActivateSheetsByValue2()
Dim mysheet1 As String
mysheet1 = Worksheets("fluff").Cells(1, 18).Value
MsgBox mysheet1
Worksheets(mysheet1).Activate
Call Macro40(mysheet1)
End Sub