Copying from multiple worksheets

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
515
I have a workbook containing the following worksheets, "Summary", "INF", "HTL", "SWM" and "NPD".

These worksheet are the exactly the same layout, that is From A1 to R7 is the Header portion and the data from A8 downwards. What I want to accomplice is to copy from the other worksheets in this workbook to the "Summary" worksheet in the same workbook.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window ( Alt F11)
Code:
d=8
for a = 2 to 5
for b = 8 to x
for c = 1 to 18
worksheets("summary").cells(d,c) = worksheets(a).cells(b,c)
next c
d= d+1
next b
d= d+1
next a
run the macro
Ravi
 

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
515
Hi,

I created a macro in a module but when I run this macro nothing seem to happen
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Try the following codes

Code:
Sub collate()
d = 8
For a = 2 To sheets.count
x = Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row
For b = 8 To x
For c = 1 To 18
Worksheets("summary").Cells(d, c) = Worksheets(a).Cells(b, c)
Next c
d = d + 1
Next b
d = d + 1
Next a
End Sub
run the macro
Ravi
 

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
515
Ravi,

Your codes seems to work to a certain extent. But one thing though is certain of the cells loss the text colour and background colour. Is there a way around this?
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
add these two lines of code to the macro just before "next C".
Code:
Worksheets("summary").Cells(d, c).font.colorindex = Worksheets(a).Cells(b, c).font.colorindex
Worksheets("summary").Cells(d, c).interior.colorindex  = Worksheets(a).Cells(b, c).interior.colorindex
and run the macro
ravi
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Why so many loops in the cell???
Code:
Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
     If ws.Name <> "summary" Then
          ws.Range("a8", ws.Range("a" & Rows.Count).end(xlUp)).Resize(,18).Copy
          With Sheets("summary").Range("a" & Rows.Count).End(xlUp).Offset(1)
               .PasteSpecial xlPasteValues
               .PasteSpecial xlPasteFormats
          End With
     End If
Next
End Sub
 

Forum statistics

Threads
1,181,607
Messages
5,930,881
Members
436,764
Latest member
avalladarez

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