activecell复制和粘贴的替代方案

Question

我有一个简单的宏，它查看A1，然后复制和粘贴到另一张表的特定范围，回到原始表，然后偏移每一列，（复制和粘贴过程）直到第T列，然后下移1行并重复，然后将表保存为PDF，这个过程重复大约100次。

这两个工作表位于同一工作簿中，该工作簿在宏运行时已经打开。

请救命！

Sub Input_Template()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

Dim E26val As String

'Go to A1 in Cost Gained sheet, look for next available cell.
Sheets("Cost Gained SelfBill").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop

Do
'1
ActiveCell.Offset(0, 0).Select
Selection.Copy
Sheets("Debit Note").Select
Range("G8,C6").Select
ActiveSheet.PasteSpecial
Range("C6").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[2]C[4], ""Q-DN"")"
'2
Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("G11").Select
ActiveSheet.PasteSpecial
'3
Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 2).Select
Selection.Copy
Sheets("Debit Note").Select
Range("G16,C22").Select
ActiveSheet.PasteSpecial
'4
Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 2).Select
Selection.Copy
Sheets("Debit Note").Select
Range("G9,G22,G24,G26").Select
ActiveSheet.PasteSpecial
'5
Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 2).Select
Selection.Copy
Sheets("Debit Note").Select
Range("G10").Select
ActiveSheet.PasteSpecial
'6
Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 2).Select
Selection.Copy
Sheets("Debit Note").Select
Range("G7").Select
ActiveSheet.PasteSpecial
'7
Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("G15").Select
ActiveSheet.PasteSpecial
'8
 Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("C9").Select
ActiveSheet.PasteSpecial
'9
 Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("C10").Select
ActiveSheet.PasteSpecial
'10
 Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("C11").Select
ActiveSheet.PasteSpecial
'11
 Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("C12").Select
ActiveSheet.PasteSpecial
'12
 Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("C13").Select
ActiveSheet.PasteSpecial
'13
 Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("C14").Select
ActiveSheet.PasteSpecial
'14
 Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("C15").Select
ActiveSheet.PasteSpecial
'15
Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 1).Select
Selection.Copy
Sheets("Debit Note").Select
Range("E26").Select
ActiveSheet.PasteSpecial
'16
Sheets("Cost Gained SelfBill").Select
ActiveCell.Offset(0, 8).Select
Selection.Copy
Sheets("Debit Note").Select
Range("C16").Select
ActiveSheet.PasteSpecial

With Sheets("Debit Note")
E26val = .Range("E26").Value
With .Range("G9,G22,G24,G26")
    Select Case E26val
        Case Is = "GBP"
            .NumberFormat = "$#,##0.00"
        Case Is = "EUR"
            .NumberFormat = "[$€-2] #,##0.00"
        Case Is = "USD"
            .NumberFormat = "[$$-409]#,##0.00"
        Case Else
            'Nothing
    End Select 'E26val
End With '.Range("G9,G22,G24,G26")
End With 'Sheets("Sheet1")

Range("B22,G16").Select
Selection.NumberFormat = "General"
Range("G15").Select
Selection.Style = "Hyperlink 2"

    Sheets("Debit Note").Select
    ChDir "P:\Feb\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "P:\Feb\" & Range("G8").Value
    'Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False


Sheets("Cost Gained SelfBill").Select
ActiveCell.Select
ActiveCell.Offset(1, -26).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Loop Until ActiveCell.Row = "20000"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

End Sub

此外，这将有助于减少我的宏行太。

谢谢你的关注

Answer 1

我已经浏览了代码的前半部分（从G9的注释8到G15的注释16)，并认为您可以通过一个循环来清理它，以节省一些时间...您继续向下行，因此For语句可以工作：

Dim i as Integer

For i = 9 to 15

    Sheets("Cost Gained Self Bill").Cells(,).Copy 'Hard to tell which cell
    Sheets("Debit Note").Cells(i,7).PasteSpecial xlValues 'Column G = column 7

Next i

您可能也可以对其他一些项目进行类似的操作。在复制部分，您可以使用类似于单元格(i+5,2）的内容，它描述了5行的偏移量，并允许第2列中的每行i持续i。