此VBA代码提示用户输入周数，然后根据该输入显示/隐藏Excel工作表中的列。最后，它将可见单元格复制到名为“Week [week number] Results”的新工作表。
我试图将代码的结果复制到excel中的新工作表，但创建的工作表是空的这是我要复制的工作表enter image description here

Sub ShowColumnsAndCopy()

' Get user input for week number or "all"
Dim weekNumber As Variant
weekNumber = InputBox("Enter the week number (1-49) or type 'all' to show all columns:")

' Check if input is valid
If Not (IsNumeric(weekNumber) And weekNumber >= 1 And weekNumber <= 49) And weekNumber <> "all" Then
    MsgBox "Invalid input"
    Exit Sub
End If

' Show first 9 columns
Range("A7:I7").EntireColumn.Hidden = False

' Show all columns if "all" is entered
If weekNumber = "all" Then
    Range("J7").EntireColumn.Resize(1, Columns.Count - 9).Hidden = False
    Exit Sub
End If

' Show columns with week number
Dim col As Integer
col = 10
While col <= 79
    If Cells(7, col).Value = "RAP Week " & weekNumber _
        Or Cells(7, col).Value = "Inventaire Week " & weekNumber _
        Or Cells(7, col).Value = "EDI Week " & weekNumber Then
        Cells(7, col).EntireColumn.Hidden = False
    Else
        Cells(7, col).EntireColumn.Hidden = True
    End If
    col = col + 1
Wend

' Show remainder columns
Range("CA7").Select
Dim lastColumn As Integer
lastColumn = ActiveCell.End(xlToLeft).Column
If lastColumn > 9 Then
    Range("J7:" & Split(Cells(1, lastColumn).Address, "$")(1)).EntireColumn.Hidden = False
End If

' Copy result to new sheet
Dim newSheet As Worksheet
Set newSheet = Worksheets.Add(After:=ActiveSheet)
newSheet.Name = "Week " & weekNumber & " Results"
Dim rngCopy As Range
Set rngCopy = Range("A7", Cells(Rows.Count, lastColumn).End(xlUp)).SpecialCells(xlCellTypeVisible)
rngCopy.Copy newSheet.Range("A1")

End Sub