VBA code Copy Data from one sheet to other sheet

[malikusmanali.awan]

Good Day!
I Was facing an issue. i want to copy data from APQ sheet to APQ history, VBA code doing fine. i need code which will not copy same data twice.
following Is code.

>>> use code - tags <<<

Sub Save_Data2()
Dim rng As Range
  Dim i As Long
  Dim a As Long
  Dim rng_dest As Range
  Application.ScreenUpdating = False
  i = 1
  Set rng_dest = Sheets("Invoice History").Range("E:J")
 ' Find first empty row in columns E:j on sheet Invoice Hsitory
  Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0
    i = i + 1
  Loop
  'Copy range B10:G24 on sheet APQ to Variant array
  Set rng = Sheets("APQ").Range("B10:G24")
 
  For a = 1 To rng.Rows.Count
    If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then
      rng_dest.Rows(i).Value = rng.Rows(a).Value
      'Copy Invoice number
      Sheets("Invoice History").Range("B" & i).Value = Sheets("APQ").Range("E4").Value
      'Copy Date
      Sheets("Invoice History").Range("A" & i).Value = Sheets("APQ").Range("H6").Value
      'Copy Company name
      Sheets("Invoice History").Range("C" & i).Value = Sheets("APQ").Range("D7").Value
      'Copy P.O number
      Sheets("Invoice History").Range("D" & i).Value = Sheets("APQ").Range("C5").Value
      i = i + 1
End If
  Next a
  Application.ScreenUpdating = True
        End Sub

 

[Marc L]

Hello, an easy way is to use an additional column as a copy status then you can filter and copy …​

​

 

[malikusmanali.awan]

Hello, an easy way is to use an additional column as a copy status then you can filter and copy …​

​

sorry marc L i didnot understand. please could you describe in detail.

 

[Marc L]

Just using a column to mark the row once copied …​

​

 

[malikusmanali.awan]

Just using a column to mark the row once copied …​

​

thanks for your respose,
please could you amend in coding?

 

[Marc L]

As guessing can't be coding …​

​

As a reminder :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[malikusmanali.awan]

As guessing can't be coding …​

​

As a reminder :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Marc L you are right but honestly speeaking i m Beginner and i just read your old comments and take the code and amend accordingly it works fine. but just facing issue what i mention in #1 now problem is what you saying is not enough for me to understand as Beginner to amend the code . it will be great help if you could eleborate.

here is the second code what you amend 2 years ago which will not copy one data twice.

>>> use code - tags <<<

 Sub Save_Data1()

 Const D = "&""¤""&", F = "TRANSPOSE(C5,E4,D7,H6,C§:C#,D§:D#,E§:E#,H§:H#)", H = 15

 Dim L%, V, Z%

 L = [B24].End(xlUp).Row: If L <= H Then Beep: Exit Sub

 With InvoiceHistory.Cells(Rows.Count, 1).End(xlUp)

 If .Row > 1 Then

 V = Application.Match(Evaluate(Replace(Replace(Replace(F, "#", L), "§", H + 1), ",", D)), _

 .Parent.Evaluate(Replace(Replace("A2:A#,B2:B#,C2:C#,D2:D#,E2:E#,F2:F#,G2:G#,H2:H#I2:I#", "#", .Row), ",", D)), 0)

 For Z = 1 To L - H: V(Z) = IIf(IsError(V(Z)), False, H + Z): Next

 V = Join(Filter(V, False, False), ", ")

 If V > "" Then MsgBox "Data already saved in" & vbLf & vbLf & "row #" & V, vbExclamation, " Operation Aborted": Exit Sub

 End If

 .Offset(1).Resize(L - H, 4).Value2 = Array([E4], [C5], [D7], [H6])

 .Offset(1, 4).Resize(L - H, 3).Value2 = Cells(H + 1, 3).Resize(L - H, 3).Value2

 End With

End Sub

 

[Marc L]

what you amend 2 years ago

? So weird as this thread is your first thread !​

​

Anyway in the original thread where you picked up this procedure​

you can compare what is missing here in your initial post in order someone can help you …​

​

it will be great help if you could eleborate.

For guessing as I'm not even a beginner - won't waste time as your duty is to follow post #6 link in order there is nothing to guess -​

so according to your poor initial post I just can elaborate some generic principle :​

use a column as a marker when a row was copied in order it is easy to filter the non copied rows when the column is empty​

and you can start just activating the Macro Recorder and operating manually …​

​

Rather than this very beginner way using an additional column you may also compare some key(s)​

in order to find out if the data already exist within the destination worksheet.​

​

 

[malikusmanali.awan]

thanks for your time. and sorry for wasting your time.
and thanks for saying me Idiot...

 

[Marc L]

for saying me idiot.

As I never wrote that …​

​

 

[malikusmanali.awan]

its written post#8. in the end.....

 

[Marc L]

LoL ‼ See post # 2 ! It's my account signature, just a famous quote … ​

​

 

[malikusmanali.awan]

any how i am disappointed from this forum i was following it from last 12 years. first time i asked something and i got nothing. i was not aware thst here nothing for beginnier this place is only for the ninja.... any how thanks for evry thing..

 

[Marc L]

Beginners have their place here if they do not confuse at least an Excel forum with some mind readers forum !​

So just follow post #6 link - see 'How to get the Best Results' section - in order any helper has nothing to guess​

and can give it a try 'cause expected essentials are missing in your initial post,​

such a mess / time waste when helpers have not much free time …​

​