help with moving cell data from multiple sheet to one sheet

New Member
I have a workbook with 32 sheets, one sheet for each day, multiple formulas on each sheet. Then I have one sheet that is the monthly totals.
I want to copy cell information from each of the 31 sheets and put them on the monthly.
ex. sheets 1-31 has a cell for total hours, on the monthly I have a column with 31 rows and I want data from the daily sheets to go here.
each sheet (1-31) hours are in the same cell. I can type in each (=sheet1!a1), but there should be an easier way. Ive tried using =sheet1!\$a1, but I'm not for sure how to make it advance one sheet.Thanks

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

jolivanes

Well-known Member
So which row(s) from each sheet do you want to copy into the master?
How many columns has each of the 31 sheets?
Do you need a cell that has the name of the sheet where is was copied from?

The first (left most) sheet in the workbook is named "Master" (no double quotes)
Data from all sheets to be copied into the Master sheet is in cell A1 in all sheets.
Data will be pasted in Column A of the Master sheet (has to be free to use)
Code:
``````Sub Is_This_A_Start()
Dim i As Long
For i = 2 To ActiveWorkbook.Sheets.Count
Sheets(i).Range("A1").Copy Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i
End Sub``````

New Member
So which row(s) from each sheet do you want to copy into the master?
How many columns has each of the 31 sheets?
Do you need a cell that has the name of the sheet where is was copied from?

The first (left most) sheet in the workbook is named "Master" (no double quotes)
Data from all sheets to be copied into the Master sheet is in cell A1 in all sheets.
Data will be pasted in Column A of the Master sheet (has to be free to use)
Code:
``````Sub Is_This_A_Start()
Dim i As Long
For i = 2 To ActiveWorkbook.Sheets.Count
Sheets(i).Range("A1").Copy Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i
End Sub``````

each of the 31 sheets has several cells I want to move, but ill focus on one right now. On all of the sheets I want column w row 79 to be added to the Maser sheet but I want it in column G rows 12-42
thanks

jolivanes

Well-known Member
Please don't quote if not absolutely required.
Just use the reply button only.
Code:
``````Sub Is_This_It()
Dim i As Long, j As Long
j = 12
For i = 2 To 32
Sheets(i).Cells(79, 23).Copy Sheets("Master").Cells(j, 7)
j = j + 1
Next i
End Sub``````

New Member
Thank You, That worked, I just changed the J to the G I wanted.
Thanks so much!

jolivanes

Well-known Member
Where does it use J in my code?
The j is not a column header in that code. The 7 is Column G.
Could you post the code after you changed it please.

Last edited:

Replies
9
Views
294
Replies
1
Views
139
Replies
4
Views
159
Replies
1
Views
32
Replies
4
Views
75

1,127,133
Messages
5,622,928
Members
415,941
Latest member
georgiana686

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back