Home>

I'm trying to create an invoice by copying the values ​​in Book A (another book) to Book B.

Postscript
As the composition of Book AB
Book A: Billing costs easily divided by item (4 sheets available)
Book B: The number of sheets is indefinite because the invoice template, information on the trading company, etc. are included.

The index is out of scope.
The object does not support this property or method.
Corresponding source code
Sub demo ()
    'Open the mirror
    Workbooks.Open "C: \ Users \ xxxxxxxxx \ Desktop \ Invoice Database.xls"
    'Activate the invoice template book
    Workbooks ("Invoice template.xlsm"). Activate

    Dim num, i, j, k, m, n As Variant
    'Delete the blue sheet *
    Application.DisplayAlerts = False
        For num = worksheets.Count To 1 Step -1
            If worksheets (num) .Tab.ColorIndex = 5 Then
                worksheets (num) .Delete
            End If
        Next
    Application.DisplayAlerts = True

    For i = worksheets.Count To 2 Step -1
        If worksheets (i) .Name = "template" Then
        Exit For
        ElseIf worksheets (i) .Name<>"template" Then
                'input
                Sheets ("template"). Range ("AA1"). Value = Format (Now, "yyyy year m month d day")
                Sheets ("template"). Range ("A4") = worksheets (worksheets.Count) .Range ("B2")Sheets ("template"). Range ("B1") = worksheets (worksheets.Count) .Range ("B3")
                Sheets ("template"). Range ("B2") = worksheets (worksheets.Count) .Range ("B4")
                Sheets ("template"). Range ("V13") = worksheets (worksheets.Count) .Range ("B8")
                Sheets ("template"). Range ("Z13") = worksheets (worksheets.Count) .Range ("B9")
                Sheets ("template"). Range ("S13") = worksheets (worksheets.Count) .Range ("B10")

                'Format
                Range ("A4, B1, B2, V13, Z13"). VerticalAlignment = xlCenter
                'Merge cells
                Sheets ("template"). Range ("AA1: AE1"). Merge
                Sheets ("template"). Range ("V13: X14"). Merge
                Sheets ("template"). Range ("Z13: AA14"). Merge
                Sheets ("template"). Range ("S13: S14"). Merge

                'Output what was input as a separate sheet
                worksheets ("template"). Copy after: = worksheets ("template")
                'Change the name and color of the output sheet above
                Sheets ("template (2)"). Name = Range ("A4")&"Invoice"
                ActiveSheet.Tab.ColorIndex = 5
                'Reconstruction of template
                Sheets ("template"). Range ("AA1: AE1"). UnMerge
                Sheets ("template"). Range ("V13: X14"). UnMerge
                Sheets ("template"). Range ("Z13: AA14"). UnMerge
                Sheets ("template"). Range ("S13: S14"). UnMerge
                worksheets ("template"). Range ("A4, B1, B2"). Clear
                worksheets ("template"). Range ("AA1, V13, Z13"). ClearContents
                Sheets (2) .Select
                'Delete the completed company sheet
                Application.DisplayAlerts = False
                worksheets (worksheets.Count) .Delete
                Application.DisplayAlerts = True
        End If
    Next'Enter from database
    Dim R As Range
    Set R = Range ("A4")
    For k = worksheets.Count To 1 Step -1
        If Workbooks ("Invoice Template"). Worksheets (k) .Tab.ColorIndex = 5 Then
            For m = 6 To Sheets ("Billing List"). Cells (Rows.Count, 1) .End (xlUp) .Row + 1
                If Workbooks ("Invoice Template.xlsm"). Worksheets (k) .R = Workbooks ("Invoice Database.xls"). Worksheets ("Invoice List"). Range ("H"&m) Then
                    Workbooks ("Invoice Template.xlsm"). Worksheets (k) .Range ("B17") = Workbooks ("Invoice Database.xls"). Worksheets ("Invoice List"). Range ("F"&m)
                End If
            Next m
        End If
    Next k
    'PDF output: Save the output PDF in the folder where this Excel is currently located.
    Dim fileName As String
    x = ActiveSheet.Name
        For j = worksheets.Count To 1 Step -1
            If worksheets (j) .Tab.ColorIndex = 5 Then
                worksheets (j) .ExportAsFixedFormat Type: = xlTypePDF, _
                fileName: = ThisWorkbook.Path&"\"&_
                worksheets (j) .Name, _
                Quality: = xlQualityStandard, _
                IncludeDocProperties: = True, _
                IgnorePrintAreas: = False, OpenAfterPublish: = True
            End If
        Next
End Sub
What I tried

When I try to embed a book or sheet into a variable, it says "Methods are not supported"
I got an error, so I solved it once.

The error itself comes from the 6th line of "Input from database".

Supplementary information (FW/tool version, etc.)

Excel 2010

vba
  • Answer # 1

    I haven't scrutinized it in detail because the layout of the sheet is unknown,
    For the time being, please modify the "'Enter from database" part as follows.

      'Enter from database
        Dim R As String
        Set R = "A4"
        For k = Worksheets.Count To 1 Step -1
            If Workbooks ("Invoice Template"). Worksheets (k) .Tab.ColorIndex = 5 Then
                For m = 6 To Sheets ("Billing List"). Cells (Rows.Count, 1) .End (xlUp) .Row + 1
                    If Workbooks ("Invoice Template.xlsm"). Worksheets (k) .Range (R) = Workbooks ("Invoice Database.xls"). Worksheets ("Invoice List"). Range ("H"&m) Then
                        Workbooks ("Invoice Template.xlsm"). Worksheets (k) .Range ("B17") = Workbooks ("Invoice Database.xls"). Worksheets ("Invoice List"). Range ("F"&m)
                    End If
                Next m
            End If
        Next k
    Commentary
      Dim R As Range
        Set R = Range ("A4")


    Means cell A4 of the active sheet of the current active book.
    With it
    Workbooks ("Invoice Template.xlsm"). Worksheets (k) .R
    You can't write like that.
    It means that the book and the sheet have already been decided, but they cannot be specified again.

  • Answer # 2

    I haven't seen the whole thing properly, but ...

    Since the information that the set R is that of the active sheet is attached,
    It cannot be used by specifying any sheet name. (Same sheet or different sheet)
    Shouldn't it just be Range ("A4")?

    If Workbooks ("Invoice Template.xlsm"). Worksheets (k) .Range ("A4") = Workbooks ("Invoice Database.xls"). Worksheets ("Invoice List"). Range ("H"&m ) Then