Forum Discussion
VBA - Copying cells from sheet to another sheet ( same File)
Hi, I have created a VBA code and it's working,
Here the RESULT: Etat_de_compte Sheet
| 01-01-2023 | 100101-01 | #REF! | #REF! | Robert Barboza | 1 949,69 $ |
The issue is that C2 and D2 has #ref! issue, but I don't want to add anything, these 2 cells should be empty, How can I fix it.
CODE:
Sub Etat_de_compte_Bouton()
'
' Etat_de_compte_Bouton Macro
Sheets("Etat_de_compte").Select
Range("A2").Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromRightOrBelow
Selection.ClearContents
' Copier ligne de la facture - No. de facture
Sheets("Facture").Select
Range("AB6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Etat_de_compte").Select
Range("B2").Select
ActiveSheet.Paste
' Copier ligne de la facture Date
Sheets("Facture").Select
Range("AC6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Etat_de_compte").Select
Range("A2").Select
ActiveSheet.Paste
' Copier ligne de la facture NOM
Sheets("Facture").Select
Range("AD6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Etat_de_compte").Select
Range("E2").Select
ActiveSheet.Paste
' Copier ligne de la facture Montant
Sheets("Facture").Select
Range("AE6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Etat_de_compte").Select
Range("F2").Select
ActiveSheet.Paste
End Sub
4 Replies
- Hi AnnieMaisonneuve, can you explain a bit more what your source data is and what you are trying to achieve?
HI, yes of course
I have a 1 workbook with 1st sheet with a Invoice template and I have 2nd sheet with timesheet data, (several rows).
The invoice sheet helps me to process each time sheet and to have the Exact total amount.$
I would like to create a report (3rd sheet) with the invoices data. For that I need to create a button from the invoice template, so I can extract data from the invoice to the report.
Each time I click the button a new row is added to the report.
I am not able to extract the data.
I would like to create a sheet inside that book to have a report of each invoices.- Hi AnnieMaisonnueve,
I think you are looking for something like this:
----------------
Sub InvoiceOverview()
' Step 1: This determines the last row of your overview sheet (change the name if necessary)
lastrow = Sheets("Etat_de_compte").Cells(Rows.Count, 1).End(xlUp).Row
'Step 2: this part of the code makes sure that the last row is never 1, so that your headers do not get replaced.
If lastrow <= 1 Then
lastrow = lastrow + 1
End If
'step 3: this part puts the data from your invoice tab into the overview, into column A, B, C and D.
Sheets("Etat_de_compte").Cells(lastrow, 1) = Sheets("Facture").Range("AB6")
Sheets("Etat_de_compte").Cells(lastrow, 2) = Sheets("Facture").Range("AC6")
Sheets("Etat_de_compte").Cells(lastrow, 3) = Sheets("Facture").Range("AD6")
Sheets("Etat_de_compte").Cells(lastrow, 4) = Sheets("Facture").Range("AE6")
End Sub
----------------
You can change the names and the ranges by yourself, but I think I adapted them to your code. This code basically takes information from one sheet and makes the cell of another sheet equal to that. If you have more information to transfer, just copy the last lines and change the cells in left part and the range in the right side of the equation.
Hope this helps 🙂
