Hello,
I am using an Excel spreadsheet for work that uses slicers to extract data from a table that is populated in Access.
We select a date range using a sliding timeline. We then hit the Refresh Button and it displays (in a Pivot Table), the non-conforming reports we generated during the date range. I then manually enter this information into a table on the NorthStar Metric Tab.
I wanted to know if it was possible to add a scan button above my table that will allow me to scan the Pivot Table area every week after I update, and then automatically display that information into my table.
Any help would be appreciated.
I could not open your table, but here is a response from ChatGPT:
Sub TransferData()
Dim LastRow As Long
' Find the last row with data in the NorthStar Metric Tab
Sheets("NorthStar Metric Tab").Activate
LastRow = ThisWorkbook.Sheets("NorthStar Metric Tab").Cells(ThisWorkbook.Sheets("NorthStar Metric Tab").Rows.Count, "A").End(xlUp).Row + 1
' Copy data from PivotTable (assuming data starts from A1 and ends at B10 - customize as needed)
Sheets("Your Pivot Sheet Name").Range("A1:B10").Copy
' Paste data to NorthStar Metric Tab in the next available row
Sheets("NorthStar Metric Tab").Range("A" & LastRow).PasteSpecial xlPasteValues
' Clean up, de-select the copied range
Application.CutCopyMode = False
End Sub
I cannot check the code because I cannot open your attachment. I assume that you have familiarly with VBA, since you mention it in your question.
Here are the comments from ChatGPT:
Here's a simple step-by-step guide:
Backup your Workbook: Before making any changes, especially involving VBA, always make a backup of your file.
Enable the Developer Tab:
Go to File > Options > Customize Ribbon.
Under the "Customize the Ribbon" pane, ensure "Developer" is checked. This will give you access to VBA and other developer tools in Excel.
Insert a Button:
Navigate to the sheet where you want the button.
Click on the Developer Tab > Insert > Button (Form Control).
Draw the button where you want it.
Assign a Macro:
Once you've drawn the button, Excel will prompt you to assign a macro.
Click "New". This will open the VBA editor.
Write the VBA Code:
Modify "Your Pivot Sheet Name" with the name of the sheet where your Pivot Table is located.
Adjust the Range "A1:B10" to cover the area your Pivot Table data occupies.
Run the Macro:
Hello Cedric,
The formula works great. Is there a way to paste across instead of down? Instead pasting straight down Column H, Paste in Columns H-N.
It will not let me upload the macro enabled book. The Analyzer-2 attachment will let you see what my workbook looks like.
I haven't looked but Trnaspose can paste from column to row and v.v.
Hi,
To make the results spill across rows (i.e., horizontally) instead of vertically, you just need to adjust the dimensions of the array.
Instead of having the array dimensioned as (1 To 5, 1 To 1), you would make it (1 To 1, 1 To 5)
Here's the modified code for that:
Function TEST() As Variant
Dim TestArray() As Variant
Dim i As Integer
' Redim the variant array to spill horizontally
ReDim TestArray(1 To 1, 1 To 5)
For i = 1 To 5
TestArray(1, i) = i
Next i
TEST = TestArray
End Function
