0

I am playing with Excel VBA to learn a littler bit of its coding. However, I have encountered myself now into a point that I do not know how to do. Here is a summary of my excel.

My am doing an excel to keep track of sales. So, on the first tap I have a summary tab where a summary of my sales will be displayed there, such as client name, PO number, data, sales value and some other more. So, for each row I have one sales record. I created a button, that when I have a new sale I click on it and it automatically creates a new row on the summary page and copy my template tab to create a new sales entry. However, they I set the code is to copy the previous row on the summary page to make it an entry to the new sale, but when I copy it i comes with the reference cells from my template tab. Is there a way for me to change the reference on the new row to the new tab I create when I click the button?

Those are my codes to insert the new row and the new tab:

Private Sub NewGP_Click()


  Range("3:3").Insert Shift:=xlUp

  Range("4:4").Select

  Selection.Copy

  Range("3:3").PasteSpecial xlFormats

  Range("4:4").Select

  Selection.Copy

  Range("3:3").PasteSpecial xlPasteFormulas

  Sheets("GP").Copy , ActiveSheet

  Worksheets("GP (2)").Visible = True

  Sheets("GP (2)").Activate

  ActiveSheet.Unprotect "password"


End Sub

And those are the cells reference that I want to change to the sheet GP (2), when I click the button.

excel snapshot reference cells

Teamothy
  • 2,000
  • 3
  • 16
  • 26
GabR
  • 1
  • 1
  • Have you taken any efforts to create the new sheet and paste to the first open row of said sheet? – Cyril Oct 28 '19 at 18:17
  • 1
    Additionally, [do your best to avoid `select` and `activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Cyril Oct 28 '19 at 18:17
  • 1
    `Range("A3:L3").Replace("GP!","",lookat:=xlPart,lookIn:=xlFormulas")` – Scott Holtzman Oct 28 '19 at 18:19
  • Hi Scott, I am sorry, but i could not use the code you have provided me. Do you have any other option of doing it? Thanks – GabR Nov 01 '19 at 14:51

0 Answers0