Hi,
Right at the end of your sub before you go back to the options page you have
the 2 lines to which I've added a third
LastCol = Range("IV1").End(xlToLeft).Column
Range("A1", Cells(LastRow, LastCol)).Name = "PivotData"
Range("IV1").Value = "Formatted"
Now in the part of your sub where you check for data you can include a check
for the value in IV1. All you now have to do is when you delete old data
ensure IV1 is include for deletion
If Range("IV1").Value = "Formatted" Then
MsgBox "Already formatted"
Exit Sub
End If
If WorksheetFunction.CountA(DataWks.Cells) = 0 Then
MsgBox "SalesForce data" & vbNewLine & "Needs to be pasted" _
& vbNewLine & "Into the Data Worksheet" & vbNewLine & "Before
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Juan Correa" wrote:
> Hello,
> I have a sub() as part of a larger set of macros.
> This particular sub() will take the raw data pulled from an in-house
> reporting system and format it to suit our needs (adding a couple of columns
> with formulas to facilitate pivot table creation).
>
> This is the code as I have it so far:
>
> Sub FormatData()
> Application.ScreenUpdating = False
> ActiveWindow.DisplayGridlines = False
>
> ' Declarations
> Dim DataWks As Worksheet
> Dim LastRow As Long
> Dim LastCol As Long
>
> ' Set the DataWks variable
> Set DataWks = Worksheets("Data")
>
>
> ' Make sure there is Data to be formatted
> On Error Resume Next
> If WorksheetFunction.CountA(DataWks.Cells) = 0 Then
> MsgBox "SalesForce data" & vbNewLine & "Needs to be pasted" _
> & vbNewLine & "Into the Data Worksheet" & vbNewLine & "Before
> Formatting", vbCritical, "Warning!"
>
> Else
>
> ' Start Formatting
> With DataWks
> .Select
> LastRow = ActiveSheet.UsedRange.Rows.Count - 6
> LastCol = Range("IV1").End(xlToLeft).Column
>
> ' Create the "Period" Column
> .Cells(1, LastCol).Copy
> .Cells(1, LastCol + 1).PasteSpecial Paste:=xlPasteFormats, _
> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> .Cells(1, LastCol + 1).WrapText = False
> .Cells(1, LastCol + 1).Value = "Booked Month"
> .Columns(LastCol + 1).AutoFit
>
> ' Populate the Month Column with new Monts
> .Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol +
> 1)).Formula = _
>
> "=IF(MONTH(H2)=12,CurrentPeriod(H2),IF(H2<=LastFriday(H2),CurrentPeriod(H2),NextPeriod(H2)))"
> .Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol +
> 1)).NumberFormat = "MM-YYYY"
>
> ' Create the "Country" Column
> .Columns("B:B").Select
> Selection.Insert Shift:=xlToRight
> Selection.NumberFormat = "General"
> .Cells(1, 1).Copy
> .Cells(1, 2).PasteSpecial Paste:=xlPasteFormats, _
> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> .Cells(1, 2).WrapText = False
> .Cells(1, 2).Value = "Country"
>
> ' Populate the Country Column with new Countries
> .Range(.Cells(2, 2), .Cells(LastRow, 2)).Formula = _
>
> "=IF(A2<>""PR"",VLOOKUP(A2,ctry_lookup,2,FALSE),IF(AND(A2=""PR"",C2=""""),VLOOKUP(A2,ctry_lookup,2,FALSE),""Distributors""))"
> .Columns("B:B").AutoFit
> End With
>
> ' Name the list range
> LastCol = Range("IV1").End(xlToLeft).Column
> Range("A1", Cells(LastRow, LastCol)).Name = "PivotData"
>
> ' Send me back to the Optioins Page
> Sheets("Options").Activate
>
> ' Turn Screen Updating Back On
> Application.ScreenUpdating = True
> End If
> Set DataWks = Nothing
> End Sub
>
> I have a check there to make sure that the raw data has been imported before
> running the format routine -> It simply checks to see that the "Data"
> worksheet isn't empty and returns a msgbox if it is.
>
> Here is what I'm trying to accomplish:
> I want to set up a second check that will stop the sub from formatting the
> raw data if it already has been formatted before.
>
> I tried adding a fourth variable like this:
> Dim formatted as boolean
> Making it true at the end of the sub() and then adding an ElseIf before the
> formatting routine starts and checking to see if formatting is true or not...
> But so far I have not been able to make it work.
>
> If someone can point me in the right direction here, I'd appreciate it.
>
> Thanks
> Juan Correa
Similar Threads
