Hello all, Im new here, I m an absolute beginer in VBA, my problem is how to make a code that can transfer data from one sheet to another sheet more than one times. In the first worksheet (INV) i create an interface to be filled up and one button to transfer that data into a table in my second sheet (MainINV). My problem is that button only worked one time. All i want is that if change something in my first sheet, like change the number or description and then when i push the button that new data is also transfered to my second sheet, under my old data. Please help me Sir.
Help to Make this Button Record data more than one times.
- Thread starter Novanbr
- Start date
Novanbr
Firstly, Welcome to the Chandoo.org forums
Why not simply clear the MainINV sheet at the start of the subroutine and then and load the data each time
see the 3 lines I added
Firstly, Welcome to the Chandoo.org forums
Why not simply clear the MainINV sheet at the start of the subroutine and then and load the data each time
see the 3 lines I added
Code:
Dim i As Integer, MINV As Worksheet, INV As Worksheet
Set MINV = Worksheets("MainINV")
Set INV = Worksheets("INV")
Dim LastRow As Integer 'Add This
MINV.Select
MINV.Range("D2").Select
LastRow = Range("A" & Rows.Count).End(xlUp).Row 'Add This
MINV.Range("A3:G" & LastRow).ClearContents 'Add This
With MINV.Range("A3:A1000")
...Thank you very much for your answer, Sir.
But unfortunatelly that is not what i want. Its deleted my old/prev data in my sheet2 (MainINV).
All I want is that if I create a new data in sheet 1 (INV), and push that button. it will check the Invoice number first, If that number exist in MainINV, it will inform the user that the invoice number is exist (already in use before-it recorded as old or prev data in sheet 2 (MainINV). But if that Invoice number is not existed in MainINV (sheet2), Data will be recorded in MainINV Table under my old data, its not deleted or replace my old/prev data.
I can do that if every data is only a single line, just like in this sample files.
But in my case, on my prev post, I have multiple line of data to be recorded, for example in the Description column at sheet1 (INV). So i have to use this kind of code:
But unfortunatelly when i use that code, i cant make the new data recorded under my old data in sheet2. My New data use the same position with my old data and replaced it. Its not what i want. I want to keep my old data, and when the new data comes its recorded under my old data, not deleted or replaced my old data. Thank you.
But unfortunatelly that is not what i want. Its deleted my old/prev data in my sheet2 (MainINV).
All I want is that if I create a new data in sheet 1 (INV), and push that button. it will check the Invoice number first, If that number exist in MainINV, it will inform the user that the invoice number is exist (already in use before-it recorded as old or prev data in sheet 2 (MainINV). But if that Invoice number is not existed in MainINV (sheet2), Data will be recorded in MainINV Table under my old data, its not deleted or replace my old/prev data.
I can do that if every data is only a single line, just like in this sample files.
But in my case, on my prev post, I have multiple line of data to be recorded, for example in the Description column at sheet1 (INV). So i have to use this kind of code:
Code:
For i = 1 To INV.Range("A5:D14").Rows.Count
If INV.Cells(i + 4, 1).Text <> "" Then
MINV.Range("C" & i + 2).Value = INV.Range("A" & i + 4).Value
End If
If MINV.Range("C" & i + 2).Text <> "" Then
MINV.Range("B" & i + 2).Value = INV.Range("D2").Value
End If
Next iBut unfortunatelly when i use that code, i cant make the new data recorded under my old data in sheet2. My New data use the same position with my old data and replaced it. Its not what i want. I want to keep my old data, and when the new data comes its recorded under my old data, not deleted or replaced my old data. Thank you.