View original (French)

Excel sheet archiving

kknd04 -  
Cemoibob Posted messages 1 Status Member -
Hello,

I'm looking to archive a sheet from my Excel workbook.

To give you some context:

I have a workbook to manage my business, and in this workbook, there are many different sheets, including one called Billing.
This sheet allows me to create invoices.

However, I would like that when I click a button, it copies this sheet into another Excel workbook (not open) named: Billing.xlsx.

It should be copied as a new sheet with the name from cell "J4".

Both workbooks are in the same folder; I would like to avoid overly precise addresses and have the macro search only in the folder of the workbook to copy from.

So, the expected process is:

Click the Archive button => copy the sheet => find the Billing workbook in the same folder => open (if needed) the Billing workbook => create a sheet => name this sheet with the content of cell "J4" => Paste the copied sheet.

I'm not sure this is the best solution; you might have something better to suggest.

Thank you in advance for your help.

Configuration: Windows / Chrome 76.0.3809.100

5 answers

  1.
    ThauTheme Posted messages 1564 Status Member 160
     
    Hello Kknd, hello forum,

    Try it like this: 
    Sub Macro1() Dim CS As Workbook 'declares the variable CS (Source Workbook) Dim CA As String 'declares the variable CA (Access Path) Dim OS As Worksheet 'declares the variable OS (Source Sheet) Dim CD As Workbook 'declares the variable CD (Destination Workbook) Set CS = ThisWorkbook 'sets the source workbook CS CA = CS.Path & "/" 'sets the access path CA Set OS = CS.Worksheets("Facturation") 'sets the source sheet OS On Error Resume Next 'error handling (in case of error, move to the next line) Set CD = Workbooks("Facturation.xlsx") 'sets the destination workbook CD (generates an error if it is not open) If Err <> 0 Then 'condition: if an error has been generated Err.Clear 'clears the error Set CD = Application.Workbooks.Open(CA & "Facturation.xlsx") 'sets the destination workbook CD (by opening it) End If 'end of the condition On Error GoTo 0 'cancels error handling OS.Copy after:=CD.Sheets(CD.Sheets.Count) 'copies the source sheet OS to the last position of the destination workbook CD ActiveSheet.Name = ActiveSheet.Range("J4") 'renames the copied sheet End Sub


    --
    See you,
    ThauTheme
    0
  2. kknd04
     
    Hello,

    Thank you for your response.

    I have an error, the second to last line according to the debugger.

    The message is:

    Runtime error '91'
    Object variable or With block variable not set

    I don't know what that means.
    0
    1. ThauTheme Posted messages 1564 Status Member 160
       
      I quickly tested it before sending it to you and I didn't encounter any errors...
      If it's line 18, maybe replacing it with: 
      Activesheet.Name = Range("J4")

      By the way, this kind of code is without safeguards. If J4 is empty or contains forbidden characters in a sheet name, you will encounter a crash...

      Ideally, if you want to wrap up this project, you should provide the file via a file hosting service like, for example: https://www.cjoint.com/
      0
  3. kknd04
     
    Change doesn't change anything.

    But I believe it's line 17 and not 18 that the debugger is showing me.
    In fact, I didn't count the End Sub line.

    So the line is:

    OS.Copy after:=CD.Sheets(CD.Sheets.Count) 'copies the source sheet OS to the last position of the destination workbook CD

    I'll see about sending the folder as an attachment

    If you have any ideas in the meantime...

    Thanks in advance
    0
  4. kknd04
     
    Hello,

    I just figured out the problem, it was really simple, the billing folder extension wasn't xlsx, but xslm.

    Thank you very much for your help.
    0
  6. Cemoibob Posted messages 1 Status Member
     
    Hello everyone
    I am looking for a code to save via a button on sheet1 in PDF format and with the name from a cell in another sheet2 of the same workbook
    Thank you for your help
    0