Hi, I am a beginner on VBA.
I am making a list with excel, users will have to type in some words in certain cells from various worksheets.
Now I would like to make a worksheet summarizing all the filled cells from the worksheets.
I found the following code which help me to extract the filled cells from one worksheet to the summary worksheet. (The code works)
But is there any code that can help me to summarize the filled cells from two or more worksheet? Say now I would like to summarize data from three worksheets, "1. material acquisition", "2. Manufacturing", "3. Usage" to the summary
sheet "Report".
Thanks a lot!
Sub MoveEM2()Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Set ws1 = Sheets("1. Material Acquisition")
Set ws2 = Sheets("Report")
On Error Resume Next
Set rng1 = ws1.Range("o13:o52").SpecialCells(xlConstants)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set rng2 = ws2.[A3]
rng1.Copy rng2
'copy column P to report B3
rng1.Offset(0, 1).Copy rng2.Offset(0, 1)
'copy column Q to report C3
rng1.Offset(0, 2).Copy rng2.Offset(0, 2)
'copy column R to report D3
rng1.Offset(0, 3).Copy rng2.Offset(0, 3)
Application.ScreenUpdating = True
End Sub