PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Sub CopyFunction() Dim lEndRow As Long, lStartRow As Long Dim num1 As Integer, num2 As Integer Dim wkb As Workbook Dim pivotSht As Worksheet, Finals As Worksheet, srcWks As Worksheet, wssheet As Worksheet Dim copyRng As Range, pasteRng As Range Set wssheet = Sheets("Summary Data") If Not wssheet Is Nothing Then Set wssheet = Sheets("Summary Data") Application.DisplayAlerts = False Worksheets("Summary Data").Delete Application.DisplayAlerts = True End If Set wkb = ThisWorkbook Set pivotSht = wkb.Sheets("Pivots") 'put error checking in, to ensure sheet "Summary Data" doesn't already exist, or you'll get an error when you set the name to the new sheet Set Finals = wkb.Worksheets.Add(after:=wkb.Sheets(wkb.Sheets.Count)) Finals.Name = "Summary Data" Finals.Range("A1:C1") = Split("ID,Sum of Debt,Sum of Credit", ",") '1st row starts from 3 lStartRow = 3 'last row = grand total and its not included lEndRow = (pivotSht.Range("A" & pivotSht.Rows.Count).End(xlUp).Row) - 1 Set copyRng = pivotSht.Range("A" & lStartRow, pivotSht.Range("C" & lEndRow)) Set pasteRng = Finals.Range("A2") 'copy values only, not formats pasteRng.Resize(copyRng.Rows.Count, copyRng.Columns.Count).Value = copyRng.Value Finals.Columns.AutoFit End Sub
Set wssheet = Sheets("Summary Data") If Not wssheet Is Nothing Then Set wssheet = Sheets("Summary Data") Application.DisplayAlerts = False Worksheets("Summary Data").Delete Application.DisplayAlerts = True End If
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Pull phone number out of cell||4||38|
|how can I scan several pages of tables with entries into Microsoft Excel 2013 for editing?||3||30|
|Create email hyperlinks from regular text||3||25|
|Cannot locate cell||15||37|
Join the community of 500,000 technology professionals and ask your questions.