Copy excel data from one sheet to another

Question

I have an excel sheet named as "Task" which contains two tabs "Data" and "DB-Task". I want to copy data from sheet "Data" to "DB-Task". Sheet "Data" has five columns (e.g. A,B,C,D,E,F). I want that if some one enter data in first row it should be transferred to another tab. If any of the columns is not filled it should give a popup to enter values before transferring data to another sheet . And In case the second row has all data it should get transferred to another sheet and only give error for first row .

I am using below code for copying data and it is successfully copying data from one sheet to another . Now I am not sure how should I use if condition effectively so that I can achieve what I want

Dim sheet1 As Worksheet
Dim sheet2 As Worksheet
Dim endrow As Long

Set sheet1 = ActiveWorkbook.Sheets("Data")
Set sheet2 = ActiveWorkbook.Sheets("Delivery Task")

Application.ScreenUpdating = False

         endrow = sheet2.Range("A" & sheet2.Rows.Count).End(xlUp).Row
         sheet1.Range("A2:E10").Copy
         sheet2.Activate
         sheet2.Range("A" & endrow + 1).PasteSpecial Paste:=xlPasteValues
         Application.CutCopyMode = False
         sheet1.Activate
         Range("A2:E10").ClearContents

End Sub

Have a look at this and its answers for some ideas: https://stackoverflow.com/q/30575923/4961700 — Solar Mike, Aug 02 '20 at 17:54
Does "it should be transferred to another tab" mean to copy in another worksheet? Anyhow, you should use `Worksheet_Change` event to trigger the cell change, check if the change has been done on the first two rows and process the data as you need. But try avoiding activating. Everything in your code can be done without it — FaneDuru, Aug 02 '20 at 18:06

score 0 · Answer 1 · edited Aug 03 '20 at 03:18

0

Eventhough I am new to VBA Let me help.You can use this sub to check if all the columns are filled for a particular row and based on the value of column 8 you can copy and transfer file

FindEmptyCells()
     Dim colnum As Long
     Dim rownum As Long
     Dim lastrow As Long
     lastrow = Sheets("Data").Range("A" & Rows.count).End(xlUp).Row
     For rownum = 1 To lastrow
     For colnum = 1 To 5
      If IsEmpty(Cells(rownum, colnum)) Then   '<== To Check for Empty Cells
     Cells(rownum, 8) = "Data Incomplete"      '<== To Enter value if empty cell found
     Else
     Cells(rownum, 8) = "OK"
     End If
     Next colnum
     Next rownum
End Sub

edited Aug 03 '20 at 03:18

Samuel Everson

2,097
2
9
24

answered Aug 02 '20 at 19:06

Anmol Kumar

157
1
7

Thanks for the help. I used your logic and somehow i am able to achieve what i want. Thanks – Deepak Verma Aug 03 '20 at 15:26
The Pleasure is all mine. :) – Anmol Kumar Aug 03 '20 at 19:33

Copy excel data from one sheet to another

1 Answers1