Macro that takes data from one sheet, adds and puts it on another sheet and deletes the information on the first.

[BAQI]

The main sheet (sheet 1) will look like the first attached image, and the other sheet (sheet 2) will look like the second attached image.

I want to take information from sheet 1, and put it on another sheet (sheet 2), and then clear the cells of sheet 1.

On sheet 1, I want it to take the "week of" in H1, and the name of the "person" in F1 for all the rows that are going to be added to sheet 2.

On Sheet 1, the letter in column "A" always correspond to the "step of task1" that is the row directly to the right of it.

On sheet 1, there will be different "tasks" (B3 for example), and total for the week (j3)

In the attached images, it'll take the four examples from sheet 1 and put it on sheet 2, then clear all the information for the example, along with the "week off" and "person". The "person" will be selected from a dropdown menu. Then once sheet one is filled out again, it'll add to the latest empy row on sheet 2.

 

[gplhl]

Would be easier if all sections - A,B, C and D had the same number of rows for tasks. without that, will these all stay the same, every week, will it always be maximum of A = 3 Tasks, B = 2 Tasks, C = 4 Tasks and D = 3 Tasks?

P.S. Try and avoid Merged cells where possible, should be banned! There are other ways to format that cause less issues for working with data

 

[BAQI]

Would be easier if all sections - A,B, C and D had the same number of rows for tasks. without that, will these all stay the same, every week, will it always be maximum of A = 3 Tasks, B = 2 Tasks, C = 4 Tasks and D = 3 Tasks?

P.S. Try and avoid Merged cells where possible, should be banned! There are other ways to format that cause less issues for working with data

I don't mind having A, B, C, D (there are more than 4 sections, but this is an example) all being 7 rows total for each, so that it's a fixed number and easier to work with.

I'm open to learning other formatting than merged cells, as I have run into issues with is before as well.

 

[BAQI]

Anyone who would like to help with this?

 

[gplhl]

If you have consistent rows declare each one as a variable, then switch sheets and enter data.

Instead of merge cells, you can use centre across selection.

This is not finished code, sorry, I don't have time to write it all, but it would look something like this, hopefully this is enough to give you the idea and hopefully finish.

Sub TransferData()

Name = Range("R1").Value ' Getting Name/Person
Week = Range("H1").Value ' Getting Week

Range("A2").Select ' Selecting A2 where A is

'' * from here is repeated however many times, could use for i to be more efficient.
Group = ActiveCell.Value ' Getting
TaskType = ActiveCell.Offset(0, 1).Value

TotalTime = Activcell.Offset(0, 9).Value
ActualTask = Activcell.Offset(0, 1).Value

Sheets("Sheet 2").Activate


Range("A2").Select
ActiveCell.Value = Week
ActiveCell.Offset(0, 1) = Name
ActiveCell.Offset(0, 2) = Group
ActiveCell.Offset(0, 3) = TaskType
ActiveCell.Offset(0, 4) = ActualTask

ActiveCell.Offset(1, 0).Activate ' Moves down one row ready for next

Sheets("Sheet 1").Activate
ActiveCell.Offset(5, 0).Select ' Moving to B

''Then repeat from *

' When Finished, clear contents
Range("B4:I6").Select
Selection.ClearContents ' repeat, this for each bit you want cleared. and may need this to start to clear Sheet 2 ready for new data.


End Sub

 

[BAQI]

If you have consistent rows declare each one as a variable, then switch sheets and enter data.

Instead of merge cells, you can use centre across selection.

This is not finished code, sorry, I don't have time to write it all, but it would look something like this, hopefully this is enough to give you the idea and hopefully finish.

Sub TransferData()

Name = Range("R1").Value ' Getting Name/Person
Week = Range("H1").Value ' Getting Week

Range("A2").Select ' Selecting A2 where A is

'' * from here is repeated however many times, could use for i to be more efficient.
Group = ActiveCell.Value ' Getting
TaskType = ActiveCell.Offset(0, 1).Value

TotalTime = Activcell.Offset(0, 9).Value
ActualTask = Activcell.Offset(0, 1).Value

Sheets("Sheet 2").Activate


Range("A2").Select
ActiveCell.Value = Week
ActiveCell.Offset(0, 1) = Name
ActiveCell.Offset(0, 2) = Group
ActiveCell.Offset(0, 3) = TaskType
ActiveCell.Offset(0, 4) = ActualTask

ActiveCell.Offset(1, 0).Activate ' Moves down one row ready for next

Sheets("Sheet 1").Activate
ActiveCell.Offset(5, 0).Select ' Moving to B

''Then repeat from *

' When Finished, clear contents
Range("B4:I6").Select
Selection.ClearContents ' repeat, this for each bit you want cleared. and may need this to start to clear Sheet 2 ready for new data.


End Sub

Thank you for the help! I appreciate it.

I tried this, and am getting:

"Compile error:

Can't assign to read-only property"

Do you have any idea why this would be?

 

[BAQI]

Thank you for the help! I appreciate it.

I tried this, and am getting:

"Compile error:

Can't assign to read-only property"

Do you have any idea why this would be?

I figured out this issue. The "Name = Range("F1").Value" line was causing the error.

I'm getting

"Run-time error '424':

Object required"

On what appears to be this line: "ActualTask = Activcell.Offset(0, 1).Value"