Solved: Copy selected cells in all worksheets and paste into another worksheet

Hi,

I need some help in creating a macro that will copy selected cells in all worksheets, apart from a couple of worksheets which will not hold the relevant data and paste the data into another worksheet in order for me to create a pivot table.

I.e. I have 3 worksheet called Summary Sheet, Attendance Template and Rawdata which should not be part of copy+paste vba code

What i need is for all remaining worksheets to copy selected cells which would be i4,i5,i6, v4,v5,v6, E13,U13, BM39, BQ39, BV39 and paste into a worksheet called Rawdata starting in Column A, Row 2, Column B, Row 2, Column C, Row 3 and so on..

As the number of worksheets will grow and existing worksheet will be updated. When the macro is run again. i would like it to simply re-write over the "Rawdata" worksheet in order for my pivot table to capture all the latest data when refreshed.

I have attached a sample workbook for easier understanding.

Thanks

BT

I'm not able to take extensive time to help with this. I also see some problems with merged cells but here is a little code that should get you started. If you can't implement it maybe someone will come along and give you a hand with specifics and I will check back when I can:

[VBA]Sub Copy_to_summary()
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "Summary" Then
Sheet.Cells(5, 2).Resize(1, 4).Copy _
Range("Summary!C65536").End(xlUp).Offset(1, 0)
End If
Next Sheet
End Sub[/VBA]
It basically excudes a sheet named summary. You can add the other sheet to the exclude line. Then it copies just one range as it is to the next line of the summary sheet. Hope this helps get you started.

Hi Lucas,

Thank you for your help in starting me off in my query.

I can certainly use what you have kindly provided to get me going in the right direction. I do however need a bit of assistant in ensuring the code does not include other names worksheets that i wish not to include in the macro.

I have tried various ways but to no avail.

Could you or anyone else please help in refining the following code to include another worksheet i.e called Rawdata that i wish not to be included in the macro.[vba]If Sheet.Name <> "Summary" Then ... rest of code[/vba]

Any help would be apprecaited.

BT

I have found some code provided by GTO via the search functionality on this site.

The code that works is

[vba]If Sheet.Name <> "Summary" _
And Not Sheet.Name = "Sample" _
And Not Sheet.Name = "Sample2" Then[/vba]

Quote:

---

Originally Posted by bananatang

...I.e. I have 3 worksheet called Summary Sheet, Attendance Template and Rawdata which should not be part of copy+paste vba code

What i need is for all remaining worksheets to copy selected cells which would be i4,i5,i6, v4,v5,v6, E13,U13, BM39, BQ39, BV39 and paste into a worksheet called Rawdata starting in Column A, Row 2, Column B, Row 2, Column C, Row 3 and so on..

---

Greetings BT,

The attached wb was missing a sheet, but no harm there.

I am hoping that part about the starting rows is a typo, and that we fill row 2, then row 3 and so on.

Finally, if my count is not off, you have eleven cells being grabbed, but only ten indicators in the header. So... If I've botched anything, may need some fine-tuning, but see if this works.

In a junk/test copy of you wb, in a Standard Module:
[vba]
Option Explicit

Sub TransferData()
Dim wks As Worksheet
Dim aryVals(1 To 1, 1 To 11)
Dim lOpenRow As Long

With shtRawData
.Range(.Cells(2, 1), .Cells(Rows.Count, 11)).ClearContents
'// Start filling at row two, as we cleared any prior run//
lOpenRow = 2

For Each wks In ThisWorkbook.Worksheets
'// Test to ensure we're not grabbing data from a sheet we don't //
'// want to. NOTE: We are using the codename of RawData, not the //
'// sheet (tab) name //
If Not wks Is shtRawData _
And Not wks.Name = "Summary Sheet" _
And Not wks.Name = "Attendance Template" Then
'// Fill an array with the vals; I think this should be faster //
'// than copying individual cells. //
aryVals(1, 1) = wks.Range("I4")
aryVals(1, 2) = wks.Range("I5")
aryVals(1, 3) = wks.Range("I6")
aryVals(1, 4) = wks.Range("V4")
aryVals(1, 5) = wks.Range("V5")
aryVals(1, 6) = wks.Range("V6")
aryVals(1, 7) = wks.Range("E13")
aryVals(1, 8) = wks.Range("U13")
aryVals(1, 9) = wks.Range("BM39")
aryVals(1, 10) = wks.Range("BQ39")
aryVals(1, 11) = wks.Range("BV39")

'// dump the array into the next available row //
.Range(.Cells(lOpenRow, 1), .Cells(lOpenRow, 11)).Value = aryVals
lOpenRow = lOpenRow + 1
End If
Next

.Range("A1:K1").EntireColumn.AutoFit
End With
End Sub
[/vba]

Hope that helps,

Mark

Edit: ACK!

Reference the destination sheet "Rawdata", change its codename to shtRawData.

GTO, thank you so much for the code and all your effort.

The code you supplied works well.

Once again, thank you very much, greatly appreciated.

All the best.

BT

You are most welcome and glad that turned out :friends: