-2

I have an excel sheet in workbook1 and I want to use VBA to copy the rows of data in that sheet according to the day(Sunday,Monday...) but to another workbook (Days) which has a separate sheet for each day.

All the examples I found were copying from one workbook to only one sheet in another workbook

Could you please help me with this?

I am using this code but when I tried to repeat it for the other days I get confused especially when to use open & save methods

Sub myTest()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If Cells(i, 2).Value = "Sunday" Then

Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\User1\Documents\Days.xlsx"
Worksheets("Sunday").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i
End Sub
simpatico
  • 11
  • 2
  • Have a look to this: https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another and then show us what you tried. – Andrea Antonangeli Aug 05 '17 at 15:58
  • Possible duplicate of [Copy from one workbook and paste into another](https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another) – ChrisM Aug 05 '17 at 18:07

2 Answers2

1

Just add more days in. The below code will also add in Monday, do the same for the rest of the week

Sub myTest()
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If Cells(i, 2).Value = "Sunday" Then

Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\User1\Documents\Days.xlsx"
Worksheets("Sunday").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If

If Cells(i, 2).Value = "Monday" Then

Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\User1\Documents\Days.xlsx"
Worksheets("Monday").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If

Next i
End Sub

You could also improve the efficiency by only opening the other Workbook once and only saving it after.

ChrisM
  • 1,576
  • 6
  • 18
  • 29
  • You might also want to move the **Workbooks.Open** before the For i = 2 and the save and close after the Next i – ChrisM Aug 05 '17 at 21:43
0

If the code you are looking at only copies to one other sheet then you could repeat the code for the other days.

ChrisM
  • 1,576
  • 6
  • 18
  • 29
  • i tried that but i dont know if i must use the open method each time or when i should save – simpatico Aug 05 '17 at 19:01
  • Looking at the code you have now added to the question does it just add the sunday lines to the sunday sheet? – ChrisM Aug 05 '17 at 20:05
  • yes all the criteria that matches Sunday copied to a sheet called Sunday in another workbook ,& i want that to be done to the whole week – simpatico Aug 05 '17 at 20:20