Solved Copy the Same Cell across Different sheets into a new sheet.

September 19, 2017 at 12:59:46
Specs: Windows 7
I am trying to create Summary sheet in excel pulling data from the same cell across many different sheets. I do not want to sum the data, I simply want to copy it. Example: For the first Summary the cell I need to copy is E2. There are 26 different sheets I need to pull this from. In the summary sheet this data will go into cells B3 - B28.

Is there an efficient way to do this?


See More: Copy the Same Cell across Different sheets into a new sheet.

Report •

#1
September 19, 2017 at 19:00:17
✔ Best Answer
Assuming that you want to copy E2 from every sheet (except for the Summary sheet) in the order they are in the workbook, this should work for you:

Sub CopyE2()
'Set first destination row
rwNum = 3
'Loop through sheets, ignore Summary sheet
 For shtNum = 1 To Sheets.Count
  If Sheets(shtNum).Name <> "Summary" Then
'Copy E2 value to Summary!Column B Row rwNum
   Sheets("Summary").Range("B" & rwNum) = Sheets(shtNum).Range("E2")
'Increment Row Counter
     rwNum = rwNum + 1
  End If
 Next
End Sub

If that is not what you want, then you'll need to supply more details.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •
Start a discussion
Related Solutions
See More ↓


Ask Question