Simple fix? (Maybe)

[Aggie2014]

Hi all,

I'm working on a macro to create an upload tab pulling data from one or more existing sheets. The way the code is written now it pulls from a specific tab name, in the example below "CMO" which is a problem because each file I have has different tab names and some have one and others have multiple, so what I'm looking to do is have the code pull from all existing tabs in the file. I know the code isn't the most efficient now, but I needed something quick so I threw this together pretty fast. Thanks in advance for the help!

Sub test_upload_macro_with_one_tab()
'
' test_upload_macro_with_one_tab Macro
'

'
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "upload"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Co"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Yr"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Bgt"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "Unit"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Code"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Projected"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Accounting"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Account"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "2019-20"
    Range("A1:F2").Select
    Selection.Font.Bold = True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=CMO!R[5]C[-3]" 
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=LEFT(CMO!R[5]C[-3],6)"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=CMO!R[5]C[3]"
    Range("D3:F3").Select
    Selection.AutoFill Destination:=Range("D3:F1000"), Type:=xlFillDefault
    Range("D3:F1000").Select
    Range("F999").Select
    Selection.End(xlUp).Select
    Range("D2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$F$45").AutoFilter Field:=4
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "300"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "2020"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "20"
    Range("A3:C3").Select
    Selection.AutoFill Destination:=Range("A3:C1000")
    
    
End Sub

 

[mumps]

It would be easier to help if you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[joeu2004]

The way the code is written now it pulls from a specific tab name, in the example below "CMO" [....] what I'm looking to do is have the code pull from all existing tabs in the file.

I think you mean "from any" worksheet?

As written, the code cannot "pull" from multiple ("from all") worksheets. And there is no simple fix to make that change, since you provide no insight into how data from multiple worksheets might be stored.

Even for the change "from any" worksheet, it is unclear how you would like that worksheet name to be selected.

Assuming that CMO was the name of the active worksheet when you recorded the macro, perhaps the following "quick fix" is the change that you are looking for.

Sub test_upload_macro_with_one_tab()
    curWS = ActiveSheet.Name
    [....]
    ActiveCell.FormulaR1C1 = "=" & curWS & "!R[5]C[-3]"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=LEFT(" & curWS & "!R[5]C[-3],6)"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=" & curWS & "!R[5]C[3]"

If CMO was not the active worksheet, but just some other worksheet, change curWS = ActiveSheet.Name to

curWS = "name of other worksheet"