According to explanation & attachment as a beginner starter to paste to the worksheet Master module :
PHP Code:
Sub Demo1() Dim V, U&, R&, N& V = [B2:D2].Value Me.UsedRange.Clear Application.ScreenUpdating = False [B2:D2].Value = V V = Application.Transpose(Worksheets(1).Range("B9", Worksheets(1).[B8].End(xlDown))) U = UBound(V) [E2].Resize(, U).Value = V R = 2 For N = 1 To Me.Index - 1 With Worksheets(N) V = Application.Transpose(.[D6].Resize(U + 3)) V(1) = .[B3].Value V(2) = .[B2].Value V(3) = .[F2].Value End With R = R + 1 Cells(R, 2).Resize(, U + 3).Value = V Next Cells(R + 1, 4).Value = "Total" Cells(R + 1, 5).Resize(, U).Formula = "=SUM(E3:E" & R & ")" Application.ScreenUpdating = True End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Re: Copy Multiple Cells from Multiple Sheets and paste them in Master Sheet
@AB33, thanks for the code, it worked like charm.
Just a small change required.
Is there anyway, I can add a snippet in the code to exclude few sheets like "Data", "Information", "Form". I mean, instead of lopping all the sheets, code needs to exclude few sheets.
Re: Copy Multiple Cells from Multiple Sheets and paste them in Master Sheet
According to this new information my revamped beginner starter to paste to the worksheet Master module :
PHP Code:
Sub Demo1() Dim V, W, U&, R&, N& V = [B2:D2].Value Me.UsedRange.Clear Application.ScreenUpdating = False [B2:D2].Value = V W = [{"Data","Information","Form"}] V = Application.Transpose(Worksheets(1).Range("B9", Worksheets(1).[B8].End(xlDown))) U = UBound(V) [E2].Resize(, U).Value = V R = 2 For N = 1 To Me.Index - 1 With Worksheets(N) If IsError(Application.Match(.Name, W, 0)) Then V = Application.Transpose(.[D6].Resize(U + 3)) V(1) = .[B3].Value V(2) = .[B2].Value V(3) = .[F2].Value R = R + 1 Cells(R, 2).Resize(, U + 3).Value = V End If End With Next Cells(R + 1, 4).Value = "Total" Cells(R + 1, 5).Resize(, U).Formula = "=SUM(E3:E" & R & ")" Application.ScreenUpdating = True End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Bookmarks