help with moving cell data from multiple sheet to one sheet

readsalot

New Member
Joined
Jan 13, 2018
Messages
14
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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


Thanks for replying.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top