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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
 

readsalot

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

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
 

readsalot

New Member
Joined
Jan 13, 2018
Messages
14
Thank You, That worked, I just changed the J to the G I wanted.
Thanks so much!
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,708
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,349
Members
416,096
Latest member
forevans

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
Top