apini
03-29-2022, 11:56 AM
Hello!
Preface this, I'm a complete beginner. I know I desperately need to take a course but I do want to get this one program running fully sooner than later.
I have a very basic program I run right now to move data from a pivot table into our import spreadsheet. It has various heads in Row 3 (Date, Description, Unit, Rate, etc) that need to be reformatted. Due to the nature of how we use this the column that those headers fall into may change based on whether we have to add or remove fields from the pivot. For example some clients are invoiced based on PO# only while others need to be broken down further by PO# and WO# which causes all columns to shift one.
What I want to do: I want to find the header (eg Date) and format all cells beneath it up to approx row 1000 in YYYY/MM/DD format. I want to do similar to Rate and Revenue into Currency format. I need my code to find the header, select ~1000 rows beneath it, reformat it. I only need specific fields formatted.
Sub Pivot_to_Range()
Application.ScreenUpdating = False
Application.Calculation = xlManual
'Clear old data in import
Worksheets("IMPORT SHEET").Range("A3:AA1000").ClearContents
'Copy Cust# to import sheet
Worksheets("PIVOT TABLE").Select
Range("H1").Copy
Worksheets("IMPORT SHEET").Select
Range("B1").PasteSpecial Paste:=xlPasteValues
'Copy from pivot headers to import headers
Worksheets("PIVOT TABLE").Select
Range("A3:AA3").Copy
Worksheets("IMPORT SHEET").Select
Range("A3").PasteSpecial Paste:=xlPasteValues
'Copy from pivot to import
Worksheets("PIVOT TABLE").Select
Range("A4:AA450").Copy
Worksheets("IMPORT SHEET").Select
Range("A5").PasteSpecial Paste:=xlPasteValues
'Find headers and reformat steps here...
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
Preface this, I'm a complete beginner. I know I desperately need to take a course but I do want to get this one program running fully sooner than later.
I have a very basic program I run right now to move data from a pivot table into our import spreadsheet. It has various heads in Row 3 (Date, Description, Unit, Rate, etc) that need to be reformatted. Due to the nature of how we use this the column that those headers fall into may change based on whether we have to add or remove fields from the pivot. For example some clients are invoiced based on PO# only while others need to be broken down further by PO# and WO# which causes all columns to shift one.
What I want to do: I want to find the header (eg Date) and format all cells beneath it up to approx row 1000 in YYYY/MM/DD format. I want to do similar to Rate and Revenue into Currency format. I need my code to find the header, select ~1000 rows beneath it, reformat it. I only need specific fields formatted.
Sub Pivot_to_Range()
Application.ScreenUpdating = False
Application.Calculation = xlManual
'Clear old data in import
Worksheets("IMPORT SHEET").Range("A3:AA1000").ClearContents
'Copy Cust# to import sheet
Worksheets("PIVOT TABLE").Select
Range("H1").Copy
Worksheets("IMPORT SHEET").Select
Range("B1").PasteSpecial Paste:=xlPasteValues
'Copy from pivot headers to import headers
Worksheets("PIVOT TABLE").Select
Range("A3:AA3").Copy
Worksheets("IMPORT SHEET").Select
Range("A3").PasteSpecial Paste:=xlPasteValues
'Copy from pivot to import
Worksheets("PIVOT TABLE").Select
Range("A4:AA450").Copy
Worksheets("IMPORT SHEET").Select
Range("A5").PasteSpecial Paste:=xlPasteValues
'Find headers and reformat steps here...
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub